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

**URL:** <https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431>\
**Category:** Ask for Help\
**Created:** [July 13, 2022, 8:30pm UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431 "2022-07-13T20:30:38Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tim/32/13534_2.png) [@Tim](https://community.glideapps.com/u/Tim)\
**Post date:** [July 13, 2022, 8:30pm UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/1 "2022-07-13T20:30:38Z")

</div>

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:

```auto
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.

```auto
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)

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 13, 2022, 10:08pm UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/2 "2022-07-13T22:08:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tim/32/13534_2.png) [@Tim](https://community.glideapps.com/u/Tim)\
**Post date:** [July 14, 2022, 7:34am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/3 "2022-07-14T07:34:54Z")

</div>

Hey Uzo

1. 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.

2. Okey, can you tell me which ones and how I need to modify this?

3. Would also work, that’s true. But shouldn’t affect calling multiple sheets. Right?

Thanks a lot for your feedback.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 7:39am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/4 "2022-07-14T07:39:56Z")

</div>

1. the data structure… 1 sheet per event is wrong… and it will only bring you more problems in the future.

2. I can write a good code for you or guide you over Zoom…

3. 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? )

---

<div class="post-metadata">

**Author:** ![Tim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tim/32/13534_2.png) [@Tim](https://community.glideapps.com/u/Tim)\
**Post date:** [July 14, 2022, 8:04am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/5 "2022-07-14T08:04:35Z")

</div>

1. 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:

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

```

Try to list multiple sheets:

```auto
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);
  
});

```

1. Would be if, point 1 is realistic, a great idea.

2. 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?

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 8:07am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/6 "2022-07-14T08:07:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 14, 2022, 8:08am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/7 "2022-07-14T08:08:37Z")

</div>

> [@Tim](#):
>
> Try to list multiple sheets:
> 
> ```auto
> 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…

```auto
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.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 8:11am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/8 "2022-07-14T08:11:45Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tim/32/13534_2.png) [@Tim](https://community.glideapps.com/u/Tim)\
**Post date:** [July 14, 2022, 8:22am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/9 "2022-07-14T08:22:11Z")

</div>

Hello Darren\_Murphy

This is my error message:

 ![Fehler](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/4/441c852e40a03223b1cc4f2707cc09d5bd5e31af.png)

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

 ![Fehler-1](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/4/c43ccda0365a651bb641526a6514a624b0253dbb.png)

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

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 8:23am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/10 "2022-07-14T08:23:07Z")

</div>

is null, because is declared before the function

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 8:32am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/11 "2022-07-14T08:32:29Z")

</div>

`var sheet = ss.getSheetByName(name);`

what is the name here?

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 14, 2022, 8:32am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/12 "2022-07-14T08:32:46Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Tim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tim/32/13534_2.png) [@Tim](https://community.glideapps.com/u/Tim)\
**Post date:** [July 14, 2022, 8:36am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/13 "2022-07-14T08:36:57Z")

</div>

Hey Uzo

That was my basis:

> [@Scripts, scripts, scripts!](https://community.glideapps.com/t/scripts-scripts-scripts/18965/41):
>
> A Script to send SMS notifications via Twilio or Email via your gmail account in case phone number is not available function Twilio() { var ss = SpreadsheetApp.getActive().getSheetByName("Notifications"); var values = ss.getRange("A2:H").getValues(); for (var i in values) { var row=i; var r = +row + 2; if (values[i][5] != '' && values[i][7] == '' ) { var number = values[i][5] var message = values[i][6] function sendSms(to, body) { …

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

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 8:37am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/14 "2022-07-14T08:37:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Tim](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/tim/32/13534_2.png) [@Tim](https://community.glideapps.com/u/Tim)\
**Post date:** [July 14, 2022, 8:45am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/15 "2022-07-14T08:45:58Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 8:51am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/16 "2022-07-14T08:51:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [July 14, 2022, 9:05am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/17 "2022-07-14T09:05:57Z")

</div>

> [@Tim](#):
>
> 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.

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 😉

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [July 14, 2022, 9:12am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/18 "2022-07-14T09:12:46Z")

</div>

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

[![small TM button](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/b/eb445376950ba207028858ee3af630b4733e0c0e.png)](https://templates-market-sa.glideapp.io/full)

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [July 15, 2022, 9:12am UTC](https://community.glideapps.com/t/how-to-call-multiple-sheets-with-the-same-script-apps-script/44431/19 "2022-07-15T09:12:52Z")

</div>

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