jeudi 1 juillet 2021

Inventory Reminders with Google Apps Scripts

Just started learning Javascript/Google Apps Script, so please be patient with me.

My goal is to create an automated email script that emails a reminder when the inventory stock has dipped below a certain level.

To start, I have a Google Sheet with 3 tabs: ContactSheet, ReorderSheet, and EmailSheet. These tabs have columns, respectively: person's name and email address; item name, number, manufacturer, current stock, and threshold for reorder; subject line and message.

Code below. No runtime errors, but the emails either do not send or they send out for only the first or last item in the spreadsheet. (Should be sending out exactly 5 emails for 5 different items.) I think the issue is with the if statement --- I'm assuming that the variables are not able to call on their respective indices, if that makes sense. I've tried converting the variables to arrays with no success. This version works best according to the logger, and only breaks down at the last block.

Thanks in advance.

function sendEmail() {
  var Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var ContactSheet = Spreadsheet.getSheetByName('ContactSheet');
  var ReorderSheet = Spreadsheet.getSheetByName('ReorderSheet');
  var EmailSheet = Spreadsheet.getSheetByName('EmailSheet');
  
  var SubjectLine = EmailSheet.getRange(2,1).getValue();
  var Message = EmailSheet.getRange(2,2).getValue();
  
  var N = ReorderSheet.getLastRow();
  
  for(var i = 2; i < N+1; i++) {
    var ItemName = ReorderSheet.getRange(i,1).getValue();
    var ItemNumber = ReorderSheet.getRange(i,2).getValue(); 
    var ItemManufacturer = ReorderSheet.getRange(i,3).getValue();
    var ItemStock = ReorderSheet.getRange(i,4).getValue();
    var ItemReorder = ReorderSheet.getRange(i,5).getValue();}

  var M = ContactSheet.getLastRow();
  
  for(var j = 2; j < M+1; j++) {
    var Name = ContactSheet.getRange(j,1).getValue();
    var EmailAddress = ContactSheet.getRange(j,2).getValue();} 
  
  if (ItemStock < ItemReorder) {
    SubjectLine = SubjectLine.replace("<item>",ItemName);
    Message = Message.replace("<name>",Name).replace("<item>",ItemName).replace("<itemnumber>",ItemNumber).replace("<manufacturer>",ItemManufacturer);
    MailApp.sendEmail(EmailAddress, SubjectLine, Message);}
}

Aucun commentaire:

Enregistrer un commentaire