How to call multiple sheets with the same script? (Apps Script)

Tim · July 13, 2022, 8:30pm

Hello

I have an app where you can book a ticket for an event. The app has about 20-30 events per year. For each event you can register and then receive an SMS confirmation. Each event has its own sheet. With App Script a function is created per sheet. A trigger that checks if there is a new entry triggers the following script and sends an SMS via Twilio:

var TWILIO_ACCOUNT_SID = 'XXXX';
var TWILIO_SMS_NUMBER = 'XXXX';
var TWILIO_AUTH_TOKEN = 'XXXX';

 function Test_SMS() {

  var ss = SpreadsheetApp.getActive().getSheetByName("Sheet-1");  
  var values = ss.getRange("A2:K").getValues();

  for (var i in values) {
     var row=i;
     var r = +row + 2;
     if (values[i][0] != '' && values[i][1] == '' ) {
       var number = '+43'+values[i][0];
       var vorname = values[i][2];
       var ticket = values[i][6];
       var datum = values[i][10];
       
       function sendSms(to, body) {
  var messages_url = 'https://api.twilio.com/2010-04-01/Accounts/' + TWILIO_ACCOUNT_SID + '/Messages.json';

  var payload = {
      "To": to,
    "Body" : 'Bestätigung: \nHallo ' + vorname + ', du hast dich angemeldet für am ' + datum + '.\n\nDeine Ticketart ist: ' + ticket + '. ',
    "From" : 'XXXX'
  };

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

  options.headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(TWILIO_ACCOUNT_SID + ":" + TWILIO_AUTH_TOKEN)

  };

  UrlFetchApp.fetch(messages_url, options);
}
       try {
      response_data = sendSms(number, vorname, ticket, datum);
      status = "sent";
    } catch(err) {
      Logger.log(err);
      status = "error";
    }
    ss.getRange(r, 2).setValue(status);
         
     } 
   
   }

  }

The problem is that for each event I have to adjust the function again and that generates a lot of code. Now I wanted to ask if it is possible to change this function so that I can call several sheets at once, so that I don’t have to customize the script individually per sheet?

I have already started an attempt here, but it only sends an SMS in Sheet-1 and also reports the following error:

TypeError: Cannot call method “getRange” from null.

var TWILIO_ACCOUNT_SID = 'XXXX';
var TWILIO_SMS_NUMBER = 'XXXX';
var TWILIO_AUTH_TOKEN = 'XXXX';

var sheetnames = ['Sheet-1', 'Sheet-2'];
sheetnames.forEach(function (name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name);
  var range = sheet.getRange('A2:K');
  var values = range.getValues();
  Test_SMS(sheet, values);
  
});

//////////////////////////////////////////////////////////////////////////////////////////////////////////


 function Test_SMS(sheet, values) {

 
  for (var i in values) {
     var row=i;
     var r = +row + 2;
     if (values[i][0] != '' && values[i][1] == '' ) {
       var number = '+43'+values[i][0];
       var vorname = values[i][2];
       var ticket = values[i][6];
       var datum = values[i][10];
       
       function sendSms(to, body) {
  var messages_url = 'https://api.twilio.com/2010-04-01/Accounts/' + TWILIO_ACCOUNT_SID + '/Messages.json';

  var payload = {
    "To": to,
    "Body" : 'Bestätigung: \nHallo ' + vorname + ', du hast dich angemeldet für am ' + datum + '.\n\nDeine Ticketart ist: ' + ticket + '. ',
    "From" : 'XXXX'
  };

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

  options.headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(TWILIO_ACCOUNT_SID + ":" + TWILIO_AUTH_TOKEN)

  };

  UrlFetchApp.fetch(messages_url, options);
}
       try {
      response_data = sendSms(number, vorname, ticket, datum);
      status = "sent";
    } catch(err) {
      Logger.log(err);
      status = "error";
    }
    sheet.getRange(r, 2).setValue(status);
         
     } 
   
   }
   
  }

I am very grateful for any help.
Many thanks

(Excuse me for my bad english)

Uzo · July 13, 2022, 10:08pm

Why do you create a new sheet per event?
Your function have variables declared before function starts, so if you trigger it, it will not set it.
To check if there are changes, simply set trigger onChange.

Tim · July 14, 2022, 7:34am

Hey Uzo

I create one sheet per event so i don’t get a huge sheet. Also, to have the whole Google spreadsheet cleaned up to remove past events. Plus the client wants one sheet per event so they can export this from the spreadsheet without deleting other content.
Okey, can you tell me which ones and how I need to modify this?
Would also work, that’s true. But shouldn’t affect calling multiple sheets. Right?

Thanks a lot for your feedback.

Uzo · July 14, 2022, 7:39am

the data structure… 1 sheet per event is wrong… and it will only bring you more problems in the future.
I can write a good code for you or guide you over Zoom…
no, you can if else the script to find the right event.

(what is the real reason for creating a new sheet? are you trying to PDF it? then you can just create a separate sheet for new events to be PDF… or maybe you can’t modify the script for text messages? that’s why you are going around and creating new sheets? )

Tim · July 14, 2022, 8:04am

Okey, isn’t it possible to just call multiple sheets and run the same script? That would actually be sufficient for what I need it for. If it is somehow possible, it would be ideal to have one sheet per bookable event.

Currently calling a single sheet:

var ss = SpreadsheetApp.getActive().getSheetByName("Sheet-1");  
var values = ss.getRange("A2:K").getValues();

Try to list multiple sheets:

var sheetnames = ['Sheet-1', 'Sheet-2'];
sheetnames.forEach(function (name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name);
  var range = sheet.getRange('A2:K');
  var values = range.getValues();
  Test_SMS(sheet, values);
  
});

Would be if, point 1 is realistic, a great idea.
Okey, I didn’t know that.

PDF are exported by the customer as soon as no more can be booked. And for him to have this easy, he just needs to export the single sheet.

The SMS notification script should not matter for the desired goal of the script. Rather, point 1 with the call of the script is more important.

I want to avoid having a huge sheet. Or do I currently not see the possibilities of the app script?

Uzo · July 14, 2022, 8:07am

yes, you can address any sheet you want, and take data from it…
i see that the problem is to create a PDF after… you can do that, without creating new sheets per event

Darren_Murphy · July 14, 2022, 8:08am

Tim:

Try to list multiple sheets:

var sheetnames = ['Sheet-1', 'Sheet-2'];
sheetnames.forEach(function (name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name);
  var range = sheet.getRange('A2:K');
  var values = range.getValues();
  Test_SMS(sheet, values);
  
});

The way you have it, it should work. Are you sure the error is being generated by that part of the code?
From your original post, I see that you have another getRange() call later in the script…

ss.getRange(r, 2).setValue(status);

Are you sure it’s not that line that triggers the error when it gets to the second sheet?
If you execute the code from the console, it’ll tell you which line number is triggering the error.

Uzo · July 14, 2022, 8:11am

error for sure is created by calling the function that has variables declared before… this script was designed for a button… not a trigger… and most likely for a web app

Tim · July 14, 2022, 8:22am

Hello Darren_Murphy

This is my error message:

And this my line at which the error is displayed (7 & 10):

The sheet.getRange(r, 2).setValue(status); only adds a text confirmation of sending the SMS to the sheet.

Uzo · July 14, 2022, 8:23am

is null, because is declared before the function

Uzo · July 14, 2022, 8:32am

var sheet = ss.getSheetByName(name);

what is the name here?

Darren_Murphy · July 14, 2022, 8:32am

Is 2-SMS-Vorlage an exact match for the name of the sheet, including case and whitespace?

Just as an aside, I would move this line…

var ss = SpreadsheetApp.getActiveSpreadsheet();

…outside the forEach() loop. ie. move it up to line 6. It should still work the way you have it, but creating a new ss object on every iteration of the loop is wasteful.

Tim · July 14, 2022, 8:36am

Hey Uzo

That was my basis:

var sheet = ss.getSheetByName(name); refers to row 7 this calls the array above.

Uzo · July 14, 2022, 8:37am

where is that variable “name” declared?
i might be drunk…but i don’t see it… maybe @Darren_Murphy will find it… he is good

Tim · July 14, 2022, 8:45am

I can’t believe it. The sheet name is identical. However, I have now copied the name again from the sheet and pasted it into the script. And suddenly it works. Something was probably still behind it.

Okey, I will do that.

My script is certainly not perfectly constructed, but it works.
Thank you very much!

Uzo · July 14, 2022, 8:51am

k, now I see it… is in the function feed… so you need to put there an array to get what you want… right now, you have the feed manually written to the function arguments…

but still… this is a wrong approach to your concept… don’t create new sheets… is pointles

Darren_Murphy · July 14, 2022, 9:05am

Yes, most likely you had a trailing white space somewhere.

By the way, I don’t disagree with most of Uzo’s comments. If this was my app, I’d probably use a single sheet, give the client an export function from the app, and get rid of the Apps Script altogether.

The problem with having multiple identical sheets is that - assuming they are connected to the app - you’re creating a lot of un-necessary work for yourself. You’ll be forever reconfiguring screens and components every time you switch sheets. I’m way too lazy to bother with that

Uzo · July 14, 2022, 9:12am

@Darren_Murphy I knew you would dig to the bottom of it… You rock!

system · July 15, 2022, 9:12am

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Script experts? Ask for Help	5	345	June 12, 2022
Searching for rows in sheet Ask for Help	22	1733	February 17, 2020
How to use Webhook to run Google sheets App Script Functions Ask for Help	4	560	September 26, 2023
Duplicating data on a form response based on a value entered Ask for Help	8	176	June 10, 2023
Apps script in glide Ask for Help	11	1537	November 5, 2022

How to call multiple sheets with the same script? (Apps Script)

Related Topics