实施GoogleSheets股票价格电子邮件警报 [英] Implementation of GoogleSheets Stock Price Email Alerts

问题描述

我有一个针对GoogleSheet中的一只股票运行的Googlesheets股票价格警报脚本.它根据目标价格检查当前价格，如果价格较低则发送电子邮件警报.如何扩展脚本以检查电子表格中的股票列表?我想我需要一个循环或向下滚动每一行的东西，但是我不确定如何做到这一点.我的单行检查工作代码:

I have a Googlesheets Stock Price Alert Script Running for a single stock in a GoogleSheet. It checks the current price against a target price and sends an email alert if it is lower. How would I expand my script to check a list of stocks in the spreadsheet? I think I need a loop or something to scroll down each row but I'm unsure of how to do this. My working code for single row check:

 // Fetch the stock price
   var stockNameRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("A2");  
   var currentPriceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("C2"); 
   var targetPriceRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2");
   var stockName = stockNameRange.getValue();
   var currentPrice = currentPriceRange.getValue();
   var targetPrice = targetPriceRange.getValue();   

 // Check stock prices
   if (currentPrice < targetPrice){

// Fetch the email address
   var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("B2");
   var emailAddress = emailRange.getValues();

// Send Alert Email.
   var message = stockName +" "+ currentPrice +" is below your target price of "+ targetPrice;
   var subject = 'Low Price Alert';
   MailApp.sendEmail(emailAddress, subject, message);
   }
 }```

推荐答案

尝试一下:

function sdk(){
  // Fetch the stock price
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet1");
  var data = ss.getRange("A13:C23").getValues(); // gets all the data from the three cols and puts it into a 2D array.
  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2").getRange("B2");
  var emailAddress = emailRange.getValues();
  var message = "";

  for (var i = 0; i < data.length; i++){ // will go over the rows that you stored in data with data[i][0] being the stock name, data[i][1] being the target and data[i][2] being the current value.
    // Check stock prices
    if (data[i][2] < data[i][1]){
      // Send Alert Email.
      message += data[i][0] +" "+ data[i][2] +" is below your target price of "+ data[i][1] + " \n";
    }
  }
   if (message != "")
      MailApp.sendEmail(emailAddress, 'Low Price Alert', message);
}

请记住，我使用的范围是 A13:C23 ，因为这是我保存测试数据的地方，您将不得不使用您的测试数据，具体取决于您的工作表结构，但是此代码会循环并在电子邮件中获取您想要的信息.它基本上会不断在消息中附加警报"行，并在循环结束时将所有内容一起发送.

Keep in mind that I used the range A13:C23 because that's where I had my test data, you will have to use yours depending on how you have structured your sheet, but this code will loop and get you the information you want into the email. it basically keeps appending rows of "alerts" into a message and sends everything together at the end of the loop.

在使用Apps脚本时，尝试限制对API的调用以使代码更优化，这就是为什么在 var ss =中获取工作表时，我更改了将范围存储到单个调用中的方式的原因... ，这也是为什么使用 getValues()很重要的原因，它只是对API的一次调用，而不是获取单个单元格的值.

When working with Apps Scripts, try to limit the calls to the APIs to make the code more optimized, this is why I changed the way you stored the ranges into a single call when getting the sheet in var ss = ..., this is also why using getValues() is important, it's only one call to the API instead of getting individual cells' values.

这篇关于实施GoogleSheets股票价格电子邮件警报的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！