0

I wrote the following simple script (my script knowledge is limited) & I wrote this to send an automated email once my inventory hit below a certain threshold but I would like to change my subject line to be the variable of the low inventory item. The line I am struggling with is var subject. Would love some help!

`

function CheckSales() {
  // Fetch the monthly sales
  var monthSalesRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Parts").getRange("E9"); 
  var monthSales = monthSalesRange.getValue();
  // Check totals sales
  if (monthSales < 21){
    // Fetch the email address
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange("B2");
    var emailAddress = emailRange.getValues();
      
    // Send Alert Email.
    var message = 'Inventory has hit and/or is below minimum threshold for' + monthSales; // Second column
    var subject = .query("C9");
    MailApp.sendEmail("example@google.com", subject, message);
    }
}`

I tried writing the query code but now that I have written that, I am met with: 9:08:07 AM Error Attempted to execute CheckSales, but could not save.

Morgan Taylor
  • 1
  • 1
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand `but I would like to change my subject line to be the variable of the low inventory item.`. Can I ask you about the detail of your expected result? – Tanaike Apr 04 '23 at 00:17
  • Hi. It's clear that you know how to use `getValue` and how to include a value in a variable, e.g. `var message`. So `var subject = .query("C9");` is completely out of character. @Carcharodon 's answer appears appropriate but I just want to check whether there was something else/something specific that you were trying to do by referencing `query`? – Tedinoz Apr 04 '23 at 06:59
  • Have you read [Can the google spreadsheet 'query' function be used in google apps script?](https://stackoverflow.com/q/17930389/1330560) – Tedinoz Apr 04 '23 at 07:45

1 Answers1

1

Assuming cell C9 is the name of the product and it's on the same sheet as the inventory amount you're checking, I would just recommend using a similar method to the one you've used to pull the monthlySales and emailAddress variables.

I would also recommend getting your active spreadsheet document once, rather than doing it each time as doing so will degrade performance, you can set it to a variable like this:

var ss = SpreadsheetApp.getActiveSpreadsheet()

And then use the ss variable moving forward like this:

var mainSheet = ss.getSheetByName('Sheet1')

Which makes the code a lot cleaner, and faster.

As for the subject line, if we get the monthly sales sheet once and assign it to a variable, we can get both the inventory count and the product name from the same sheet without having to get the sheet multiple times.

function CheckSales() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  // Fetch the monthly sales
  var monthSalesSheet = ss.getSheetByName("Parts") 
  var monthSales = monthSalesSheet.getRange("E9").getValue();
  // Check totals sales
  if (monthSales < 21) {
    // Fetch the email address
    var mainSheet = ss.getSheetByName("Sheet1")
    var emailAddress = mainSheet.getRange("B2").getValue()

    var productName = monthlySalesSheet.getRange("C9").getValue()
      
    // Send Alert Email.
    var message = 'Inventory has hit and/or is below minimum threshold for' + monthSales; // Second column
    var subject = `${productName} has low inventory`
    MailApp.sendEmail("example@google.com", subject, message);
    }
}

Again, this was making some assumptions about the structure of your document, if this isn't accurate please post more details about the document to help narrow it down.

Carcharodon
  • 38
  • 5