0

I am using google sheets to record our printer cartridge inventory, and using script editor to check the cells C2:C50, and send out an alert email if one cell falls below 1 after editing. I finally have it set up where it will work if C2 falls to 0, but that is it. If C3:C50 go to 0 while C2 is >=1, nothing goes out. If C3:C50 go to 0 while C2 is 0, an email will be sent out for C2 only. I'm trying to figure out how to get the function to look at C3:C50. And its not giving out any errors when running. Just not working as expected.

This is what I got so far...

function CheckInventory()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CartInv");
var range = sheet.getRange('C2:C50'); \\**I think the problem is here somewhere!**
var ui = SpreadsheetApp.getUi();

if (range.getColumn() <= 3 &&
    range.getLastColumn() >= 3)
{
   var row = range.getRow();
   var values = SpreadsheetApp.getActiveSheet().getRange(row,3).getValues();

   if(values<1)
   {
    var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CartInv").getRange("K2");
    var emailAddress = emailRange.getValues();
    var cartridge = SpreadsheetApp.getActiveSheet().getRange(row,1).getValue();
    var message = "ATTENTION! "+cartridge+" is out. Order more.";
    var subject = 'Test Awareness Mail';
    MailApp.sendEmail(emailAddress, subject, message);
}
}  
}

The main problem I'm running into is that if(values<1) reads only C2. I'm trying to get it to send an email for every cell in the range C2:C50

  • What do you mea by "I'm trying to figure out how to get the function to look at C3:C50."? Are you getting an error message? How are you calling the function? – Rubén Feb 21 at 16:22
  • The C column is used for keeping track on the number of certain cartridges we have on hand so when one falls to 0, we want to get an email notice. With how this is set up, we only get the notice if C2 is 0. I'm trying to find a way for this to work on the other cells as well. And I'm not sure what you mean by "calling the function" I'm really new to this. – Richard Feb 21 at 16:36
  • What steps you follow in order to make that the CheckInventory function run? Are you running it directly from the Google Apps Script editor or a you using a button (image), custom menu, trigger, google.script.run (client-side code), ...? Have you reviewed the execution transcript or the StackDriver logs (ref developers.google.com/apps-script/guides/support/…)? – Rubén Feb 21 at 16:41
0

Partial answer:

This part of your code doesn't make sense

if (range.getColumn() <= 3 &&
    range.getLastColumn() >= 3)
{
   var row = range.getRow();

The above because the range is always the same (var range = sheet.getRange('C2:C50');)

getValues() return a multidimensional array, so values<1 doesn't make sense either.

NOTE: Your script isn't sending the email messages because values<1 returns false, so the code block of the if statement is skipped.

It's very likely that you should start by reading https://developers.google.com/apps-script/guides/sheets. There you will find (links aren't included)

If you're not familiar with arrays in JavaScript, Codecademy offers a great training module for arrays. (Note that this course wasn't developed by and isn't associated with Google.)

There are other sources that you could use to learn the basics of arrays in JavaScript as well as a lot of related questions here and in https://stackoverflow.com

| improve this answer | |
  • I rigged this whole function up after reading through a few other set ups that were similar enough to work with. And the best I can get from the .getColumn and .getLastColumn is that it helps focus on the 3rd column for when editing happens. Without it, we get emails if any cell is edited. We just want emails if a cell in the 3rd column gets edited. And no errors come up when it runs, so it should mean that everything is at least being satisfied, but since I don't get any emails about the other cells after C2, I'm assuming I'm missing something. – Richard Feb 21 at 17:31
  • In response to your note, that's what I started noticing after playing some more. I guess the main question now becomes how do I get it to read all cells included in the range, and then send out an email based only on the one I just edited? – Richard Feb 21 at 19:14
  • @Richard Please edit your question to add your progress and to make your question more specific. – Rubén Feb 21 at 19:14
  • I think I'm going to go a different route. I have a new one written that will read all the cells in I need it to, and send an email at the end of the day, so it works good enough to get done what we needed. I'm gonna look at those articles you suggested, and try to get some more experience under my belt before trying this one again. – Richard Feb 21 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.