Function that operates on multiple sheets on a schedule

[Dallin Watson]

Hi everyone, 

I have created a script that sends SMS from a sheet using Twilio. It works great, but I only know how to make it run using getActivesheet, which doesn't help me accomplish the end goal of having this operate on up to 120 different sheets daily. 

What I want it to do is have it triggered either daily at 8pm or check for an update to each sheet every hour, and then send the text with the data in sendAll to the number on the sheet. 

function sendSms(to, body) {

  var messages_url = "twilioaccount";

  var payload = {

    "To": to,

    "Body" : body,

    "From" : "MYTWILIONUMBER"

  };

  var options = {

    "method" : "post",

    "payload" : payload

  };

  options.headers = { 

    "Authorization" : "Basic " + Utilities.base64Encode("AUTHINFO")

  };

  UrlFetchApp.fetch(messages_url, options);

}

function sendAll() {

  var sheet = SpreadsheetApp.getActiveSheet();

  var preFlight = "Pre-Flight " + sheet.getRange("B3").getValue();

  var flight1 = sheet.getRange("A4").getValue();

  var flight1Time = sheet.getRange("B4").getValue();

  var flight1Type = sheet.getRange("C4").getValue();

  var phoneNum = sheet.getRange("B10").getValue();

  

  var schedule = preFlight+" "+flight1+" "+flight1Time+" "+flight1Type

  

sendSms(phoneNum, schedule);

  

}

function myFunction() {

  sendAll();

}

Do I create a sheet addon out of this, and then attach it to every sheet? Or use getsheetbyid and add all 120 sheet IDs into the code and have it loop over them all some how?

Thanks for any suggestions!

[Dallin Watson]

I am wondering if a better way would be to load each sheet by id (openById).

Is it possible to run a function from one script on multiple sheets by calling them this way, say looping through a list or array?

[Clark Lind]

Yes, you should be able to do this.  If you have a lot of things to send, you will quickly run into quota problems and the 6-minute script execution timelimit. You may want to do them in batches of 25-50 at most so Google doesn't shut you down. Even then, you will have a max daily quota.

Something like this should work:

function sendAll(sheetName) {  //pass sheet name to sendAll
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); //get the appropriate sheet and do the work

  var preFlight = "Pre-Flight " + sheet.getRange("B3").getValue();
  var flight1 = sheet.getRange("A4").getValue();
  var flight1Time = sheet.getRange("B4").getValue();
  var flight1Type = sheet.getRange("C4").getValue();
  var phoneNum = sheet.getRange("B10").getValue();
 
  var schedule = preFlight+" "+flight1+" "+flight1Time+" "+flight1Type
 

sendSms(phoneNum, schedule);

 
}

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets(); //get all the sheets (tabs) in the Spreadsheet
  for (var i=0;i<ss.length;i++) {  //loop through them all and get the name and pass the name to the sendAll function
       sendAll(ss[i].getName());
     }
}

[Dallin Watson]

Thanks for the reply! There would be about 60 total sends per day.

What if they are different spreadsheets, not sheets/ tabs in a spreadsheet? That’s why I was thinking of using openById...

Can I use this method with openById... like making a list of all spreadsheet IDs in the script and parsing those or listing then in a column in another sheet and calling them into the sendall?

[Dallin Watson]

I should clarify. The script would only run once per day, but pull and send data from about 60 sheets. The data captured is small less than 100 chars from each sheet.

[Clark Lind]

Yes, essentially the same thing. The way I would probably do it is, put all the sheets in the same folder. Have a script that runs maybe 3-4 hours before I want to send. This script would get the folder, and get all the files within, getting all the fileIDs, then write them all to a Tab in a sheet. 

Then your original script can open this master sheet, get all the file IDs, then run through them one at a time. 

If the Sheets are not all together, then manually do the above, copying/pasting into a master sheet manually.  

[Dallin Watson]

Thank you so much, you've been super helpful. 

I'm pretty new to this. What I've done so far is gone with the idea of just entering all the desired sheet IDs into a master sheet, and then parse that for the IDs.

However, I've hit a mental block on how to pass those to the sendall function... 

here's what I did to get the ids from the master sheet

---------------------------------------------------------------------------------

function myFunction() {

  

var masterSheet = SpreadsheetApp.openById("1xRt3mnzrz-rDxHqOwBPY2vc9sn0e0MlZy22DyTPpuRA");

  

var idTable = masterSheet.getSheetByName('Sheet1'); 

var startRow = 1;

var numRows = idTable.getLastRow();

var dataRange = idTable.getRange(1, 2, numRows, 1);

var data = dataRange.getValues();

  

  for (i in data) {

    

    var rows = data[i];

    

    Logger.log(rows);  // to see the output

  }

[Clark Lind]

Ok. At this point, I'll assume each sheet (tab) within each Spreadsheet is named the same...say, Sheet1, for convenience. 

Something like this should work:

change the for loop in myfunction to:

for (i in data) {
   
    sendAll(data[i]);  //pass the Spreadsheet ID to sendAll
}

Then modify sendAll to this:

function sendAll(sheetID) {  
 //Option1: all source sheets are named Sheet1:
  var sheet = SpreadsheetApp.openById(sheetID).getSheetByName("Sheet1");

 //Option2 move all the source tabs/sheets to the first (left most) position in each spreadsheet
 // var sheets = SpreadsheetApp.openById(sheetID).getSheets(); //get all the tabs in the Spreadsheet
 // var sheet = sheets[0]; //get the left-most tab

  var preFlight = "Pre-Flight " + sheet.getRange("B3").getValue();
  var flight1 = sheet.getRange("A4").getValue();
  var flight1Time = sheet.getRange("B4").getValue();
  var flight1Type = sheet.getRange("C4").getValue();
  var phoneNum = sheet.getRange("B10").getValue();
 
  var schedule = preFlight+" "+flight1+" "+flight1Time+" "+flight1Type
 


sendSms(phoneNum, schedule);


 
}

[Dallin Watson]

Awesome. It works like a charm!

Thanks for your help!! 

[Clark Lind]

Awesome! Glad to help.

[Dallin Watson]

So I've forked off another component of this idea, and that is to receive confirmation sms that the individual got their schedule via text.

I have it working where upon executing the script, it will gather all the sms received by the Twilio API that day, but what would be really cool is to be able to have it set the value of dataAll.messages adjacent to a column with the name of the individual who the phone number belongs to.

Initially I was thinking another sheet with a set of columns for name/phone number to reference, but maybe it's easier than that. 

If I fill columns 1 & 2 (A & B) with a static list of names and phone numbers, could I get the else if segment (or a new loop) to compare dataAll.messages.from to column 2 (or hidden table/other sheet) and then setValue of adjacent columns with the corresponding .date .time. .body ?

// example

var theSheet = SpreadsheetApp.getActiveSheet();

var theRow = 3;

var startColumn = 2;

var dataAll = JSON.parse(response.getContentText());

for (i = 0; i < dataAll.messages.length; i++) {

   theColumn = startColumn;

   // -------------------------------------

   // Date and Time

   rowDate = dataAll.messages[i].date_sent;

   var theDate = new Date (rowDate);

   var today = new Date();

   today.setHours(0,0,0,0);

   if(isNaN(theDate.valueOf())) {

      theDate = 'Not a valid date-time';

      theColumn++;

      theColumn++;

   }

   else if (theDate > today) {

     theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].from);

     theColumn++;

     

      theDate.setHours(theDate.getHours()+hoursOffset);

      theSheet.getRange(theRow, theColumn).setValue(theDate);

      theColumn++;

      theSheet.getRange(theRow, theColumn).setValue(theDate);

      theColumn++;

     

       theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].body);

     theRow++ 

  

   

   }

// example

[Clark Lind]

The way I do it, I add another column next to my source data, then fill the column with checkboxes. I assume the Twilio data has some identifying information (like phone number) that is unique to each person. Use that to parse the Twilio data to your source data (checking Twilio phone# against source phone #.), if == then mark checkbox as TRUE. [the checkbox is just a boolean True or False; True==checked]. If starting a new schedule or session or something, get the checkbox column range & set the values to False (unchecked).  You could even take it further and add two checkbox columns "Sent" and "Received". Set Sent to True when it initially runs, then set Received to True upon verification of receipt. 

It might be better to do it all on a separate Tab. Then you can build another function to check the Received column, grab all the False results (unchecked), then send again, etc etc.. Depending on your rules/needs for verification, add more columns with checkboxes for each attempt, stopping at your max # of attempts or something. I wouldn't send more than twice for non-emergency messages. If it is emergency-related, go to the secondary method, like emailing a form attached to the Spreadsheet. Then set a trigger for onFormSubmit() to check emails against source emails, etc etc. There are many things you can do :)  

[Dallin Watson]

I started using the following change, but I realized that basing my loop on data length might have unexpected results. My sheet to capture this data will have a list of up to 70 employees and their phone numbers, but if only half reply to the SMS, then the loop might not make it down the full list to check the boxes, right? Is there a better way to do this? 

The purpose is to verify that everyone received their schedule based on the fact they replied to the SMS sent by the previous script. All it does is check for an SMS received today from the employees phone number. 

// Parse the JSON data and put it into the spreadsheet's active page.

// Documentation: https://www.twilio.com/docs/api/rest/response

var theSheet = SpreadsheetApp.getActiveSheet();

var theRow = 3;

var startColumn = 2;

var phoneColumn = 7;

var checkColumn = 6;

var pilotNum = theSheet.getRange(theRow, phoneColumn).getValue();

var dataAll = JSON.parse(response.getContentText());

for (i = 0; i < dataAll.messages.length; i++) {

   theColumn = startColumn;

   // -------------------------------------

   // Date and Time

   rowDate = dataAll.messages[i].date_sent;

   var theDate = new Date (rowDate);

   var today = new Date();

   today.setHours(0,0,0,0);

   

  if (theDate > today && pilotNum == dataAll.messages[i].from) {

    

    theSheet.getRange(theRow, checkColumn).setValue("TRUE");

    theRow++

    

//     theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].from);

//     theColumn++;

//     

//      theDate.setHours(theDate.getHours()+hoursOffset);

//      theSheet.getRange(theRow, theColumn).setValue(theDate);

//      theColumn++;

//      theSheet.getRange(theRow, theColumn).setValue(theDate);

//      theColumn++;

//     

//       theSheet.getRange(theRow, theColumn).setValue(dataAll.messages[i].body);

//     theRow++