Google Sheets built-in "On form submit" trigger

Ask for Help
21

I assume you can still use an on change trigger, take out the row number of the row which triggers the script and use those values to send an email/SMS.

You can use Zapier/Integromat to do the same thing with emailing/Twilio.

1 Like
22

Yes, I have it working with Zapier/Twilio but there is a 2-3 minute lag. It’s the lag that is driving me to want the GAS to work.

23

Once the email/SMS is sent, then presumably another box is checked?
Your script can use this, ie: (pseudocode)

if (send_email is checked && email_sent not checked) then
  send email
  check email_sent box
end if

So you would need to read the entire sheet and check each row one by one.
A word about that…
The temptation is to create a loop and do a series of getRange().getValues calls, one for each row.
Don’t do that - it’s very slow (ask @Eric_Penn :wink:).
Much better to read the entire sheet into memory using a single getDataRange() call, and then do the processing in memory. This is orders of magnitude faster, especially if your sheet has any more than a few hundred rows.

2 Likes
24

Emailing works fine, text message would require you to have the carrier’s emailing template.

25

Some data consider:

SMS triggered by onChange with direct connection to Twilio: almost instantaneous SMS received.

SMS triggered by Sheets-Zapier-Twilio: 2-3 minutes

SMS triggered by sendTxt approach using “number@txt.att.net”: 60-90 seconds

May not sound like a lot, but it feels like a lifetime for certain apps that come to life with the real-time SMS. I will continue to try to make the onChange direct to Twilio method work for my different use cases and will report back I as learn more.

Thank you for the input.

1 Like
26

I hear what you are saying about reading the entire range. But, let’s say the sheet has 100 rows and each row has a yes/no column for “accepted.” At a given point in time, there may be 60 columns with the default value of “no” and 40 columns that have been “checked” and are “yes.” When a user checks the box on a row (one of the 60 that are “no”) and converts it to a “yes,” I need to pull that row of data, which includes a cellphone number so I can send an SMS that indicates “accepted” has been changed to “yes.” Ideas welcome. Thank you.

27

Do you record the fact that the SMS has been sent?

28

As far as I’m aware, when Glide changes data in your sheet, it will only ever trigger an On Change event, even if it’s just (as you describe) a value being changed in a single cell. As you’ve probably seen from the docs, the On Change event doesn’t have a range attribute, so there is no way to directly see which cell was changed. So I don’t think there is any getting away from processing the whole sheet.

What I would do is add another column that indicates whether or not an SMS has been sent. So then when you process the sheet, you look for rows where “accepted” is true and “SMS Sent” is false. For any that you find, you send an SMS and set “SMS Sent” to true.

If you’re worried about processing time, I wouldn’t. As long as you’re doing a single read (getValues()) and a single write (setValues()), then processing a sheet of 100 rows (or even 1000) should take less than a second.

2 Likes
29

Ok, I think I see the approach. Will see if I can implement. Thank you

30

Actually, just thinking about this a bit more - if you write everything back in one go then you risk a race condition. ie. if 2 users happen to send an update at the same time.

So it’s probably safer to just flag the rows that have changed as you’re processing the data, and then update the “SMS Sent” flags one at a time once you’re done. Assuming that only one or a few rows will have changed each time, the additional processing overhead should be negligible.

2 Likes
31

Trying to set up the new column to say “Done” once a text message has been sent. The following code not working.

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var s = ss.getSheetByName("Bets2");
   var r = s.getActiveCell();
      if(r.getColumn() == 7 && r.getValue() == 'TRUE') {
      var nextCell = r.offset(0, 25);
      nextCell.setValue("Done");
 }
}```

Any ideas appreciated.
32

A bit difficult to say without more context, but a couple of comments…

It seems a bit odd to be using getActiveCell() here. That’s going to return the currently active cell in the sheet, which could be anywhere.

So the the column that contains “accepted” is column number 7, and you want to change the value in column 32 to “Done”. Is that correct?

Maybe if you can show me your script in its entirety, I can offer some better advice.

1 Like
33

@gp9293 - here is an example of how I would do it…

function check_sms() {
  var sheetname = 'SMS'; // The name of the sheet to check
  var check_col = 6; // The column number that contains "Accepted"
  var flag_col = 9; // The column number that should be changed to "Done"
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var data = sheet.getDataRange().getValues();
  data.shift(); // Discard the header row
  var rows_to_flag = [];
  var row = 2;
  while (data.length > 0) {
    var this_row = data.shift();
    // NB. Arrays are zero-based, so we use col number -1
    if (this_row[check_col-1] == 'Accepted' && this_row[flag_col-1] != "Done") {
      // Call send SMS function here
      rows_to_flag.push(row);
    }
    row++;
  }
  
  if (rows_to_flag.length > 0) {
    rows_to_flag.forEach(function (row) {
      sheet.getRange(row,flag_col).setValue('Done');
    });
  }
}

If I’m understanding correctly, you should almost be able to use that as it is - just change the values of the variables on the first 3 lines.

2 Likes
34

So this would trigger on an onEdit, correct? If the edit changed the value of column 7 to “Accepted” then the send SMS function would run and then the value of column 24 would be changed to 'Done." Am I understanding the logic? Does the script stop if there is no SMS function? eg, In testing, I go into the sheet and change a column 7 to “Accepted.” Doing this does not change column to 'Done" since am not running the sendSMS function. Make sense?

35

You need to create an On Change (not On Edit) trigger that would fire it when a change is made.

I would wrap it in another function that first checks which sheet was changed, before calling it. This would ensure that it isn’t called unnecessarily. Something like this:

function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  if (sheetname == 'SMS') {  // Change this to the name of your sheet
    check_sms();
  }
}

Then you create an On Change trigger that fires the above function. Once you’ve done that, any edits that are made to that sheet should fire it.

Yes, except the column numbers are different in my example.

Correct. But you can test it by changing one of those values and then run the script manually.

The script will stop as soon as it’s finished processing. If there is no SMS function, it will still set any rows that qualify as “Done”, but no SMS will be sent (obviously).

1 Like
36

Script works! Now am having trouble nesting the sendSMS function. This will send the SMS to notify user of the “Accepted” change. I have the email working (at the bottom of the script), but cannot get the SMS to trigger. The “function sendSMS” block is code that works for me in a different script, but I must be improperly nesting it or something. Any advice appreciated.

  var sheetname = 'Bets2'; // The name of the sheet to check
  var check_col = 13;
  // The column number that contains "Accepted"
  var flag_col = 24; // The column number that should be changed to "Done"
  var cellB_col = 11;
  var cellA_col = 5;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  //testing to see if script ran
  var range = sheet.getRange("r2");
  var data = range.getValue();
  range.setValue(++data);
  //end testing
 
  var data = sheet.getDataRange().getValues();
  data.shift(); // Discard the header row
  var rows_to_flag = [];
  var row = 2;
  while (data.length > 0) {
    var this_row = data.shift();
    // NB. Arrays are zero-based, so we use col number -1
    if (this_row[check_col-1] == 'YES' && this_row[flag_col-1] != "Done") {
      if (rows_to_flag.length > 0) {
    rows_to_flag.forEach(function (row) {
      sheet.getRange(row,flag_col).setValue('Done');
    });
      
      // Call send SMS function here

   function sendSMS(to, body) {}
 
  var messages_url = "https://api.twilio.com/.....";
 
  var payload = {
    "To": "1562xxxxxxx",
    "Body" : "Sample text for the SMS." ,
    "From" : "1410xxxxxxx"
  };

  var options = {
    "method" : "post",
    "payload" : payload
  };

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("AC291dxxxxxxxee")
  };
 UrlFetchApp.fetch(messages_url, options);
}

//Test if email works
      MailApp.sendEmail('gpillari@gmail.com', this_row[cellA_col-1], this_row[cellB_col-1]);
 //End Test

      rows_to_flag.push(row)
      console.log(range, check_col, flag_col, cellB_col)
    }
    row++;
  }
1 Like
37

Instead of nesting the entire function, you just need to include a call to it.
So you should have something like:

// Call send SMS function here
sendSMS(to, body);

Note however that your sendSMS function expects two parameters, so you need to define and populate those variables before you call the function. So just prior to the function call, you might have something like:

var to = 'xxxxxx'; // A value already captured from the sheet?
var body = 'Text of message';
2 Likes
38

Your knowledge of scripts is great :+1: brilliant.

2 Likes
39

hahaha, thanks. But that’s not really true. Like most things, I am continually reminded that the more I learn about Apps Scripting, the more I find out that I don’t know :wink:

2 Likes
40

And because of this approach you know a lot more than you think you know :grin:

1 Like