# Scripts, scripts, scripts!

**URL:** <https://community.glideapps.com/t/scripts-scripts-scripts/18965>\
**Category:** Ask for Help\
**Created:** [November 26, 2020, 6:15pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965 "2020-11-26T18:15:21Z")\
**Posts on this page:** 20\
**Page:** 3

<div class="post-metadata">

**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\
**Post date:** [December 31, 2020, 3:52am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/41 "2020-12-31T03:52:26Z")

</div>

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 messages_url = "Your Twilio URL";

  var payload = {
    "To": to,
    "Body" : body,
    "From" : "Twilio phone number"
  };

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

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("Your API Code")
  };

  UrlFetchApp.fetch(messages_url, options);
}
       try {
      response_data = sendSms(number, message);
      status = "sent";
    } catch(err) {
      Logger.log(err);
      status = "error";
    }
    ss.getRange(r, 8).setValue(status);
       
      
         
     }  
    if (values[i][5] == '' && values[i][7] == '' ) {
         Utilities.sleep(1000);
      var mail = values[i][2]
       var message = values[i][6]
       var row = +i + 2
       ss.getRange(row, 8).setValue("Email Sent");
         MailApp.sendEmail(mail, "Your Subject line", message, {name:"name of sender"})
        
       SpreadsheetApp.flush();
       }
  }
}  

```

This script runs every minute and checks if there are new rows in the sheet, if there are, it will pull up the number, body of the SMS and send an SMS via Twilio. (In case there’s no number, it sends a mail)  
Once it’s run, it sets a value in a trigger column which makes sure that the message is only sent once to each user.

---

<div class="post-metadata">

**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\
**Post date:** [December 31, 2020, 3:56am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/42 "2020-12-31T03:56:55Z")

</div>

A Script that automatically appends rows in another sheet based on predefined conditions, I use this script in a meal scheduler app where I need to create schedules for meal plans based on the number of meals, the category, the time etc.

```
function Scheduler() {
  var ss = SpreadsheetApp.getActive().getSheetByName("Customer Sheet");
var values = ss.getRange("A2:AT").getValues();
   for (var i in values) {
 if (values[i][16] && values[i][15] == 'Creating Schedule') {
   var row = i
   var time = new Date();
   var customer = values[i][0];
   var sdate = values[i][3];
   var day = values[i][13];
   var M1 = values[i][6];
   var M2 = values[i][7];
   var M3 = values[i][8];
   var M4 = values[i][9];
   var M5 = values[i][10];
   
   if (values[i][6] != '' && values[i][15] == 'Creating Schedule' ) {
   Utilities.sleep(100);
   var row = +i + 2       
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Schedule Sheet')
       sheet.appendRow([customer,sdate,day,M1,"M1"]);           
   SpreadsheetApp.flush();
   }
    if (values[i][7] != '' && values[i][15] == 'Creating Schedule' ) {
   Utilities.sleep(100);
   var row = +i + 2       
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Schedule Sheet')
       sheet.appendRow([customer,sdate,day,M2,"M2"]);           
   SpreadsheetApp.flush();
   }
    if (values[i][8] != '' && values[i][15] == 'Creating Schedule' ) {
   Utilities.sleep(100);
   var row = +i + 2       
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Schedule Sheet')
       sheet.appendRow([customer,sdate,day,M3,"M3"]);           
   SpreadsheetApp.flush();
   }
    if (values[i][9] != '' && values[i][15] == 'Creating Schedule' ) {
   Utilities.sleep(100);
   var row = +i + 2       
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Schedule Sheet')
       sheet.appendRow([customer,sdate,day,M4,"M4"]);           
   SpreadsheetApp.flush();
   }
    if (values[i][10] != '' && values[i][15] == 'Creating Schedule' ) {
   Utilities.sleep(100);
   var row = +i + 2       
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName('Schedule Sheet')
       sheet.appendRow([customer,sdate,day,M5,"M5"]);           
   SpreadsheetApp.flush();
   }
 }
   }

}
```

---

<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:** [December 31, 2020, 4:12am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/43 "2020-12-31T04:12:58Z")

</div>

I like my GSheets to look “pretty”, so in most of my projects I have the following running in the background with an On Change trigger:

```
function format_sheet(sheetname) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  sheet.getBandings().forEach(function (banding) {
    banding.remove();
  });
  var range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  range.applyRowBanding(SpreadsheetApp.BandingTheme.BLUE);
  range.setBorder(true, true, true, true, null, null, null, null);
  range.setBorder(null, null, null, null, true, true, null, SpreadsheetApp.BorderStyle.DOTTED);
}

```

The end result is that all my sheets look something like so:

 ![Screen Shot 2020-12-31 at 12.11.48 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/4/44679dbdce138064b9e90540e58023ef8b378e90.png)

---

<div class="post-metadata">

**Author:** ![wjcv06](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wjcv06/32/12747_2.png) [@wjcv06](https://community.glideapps.com/u/wjcv06)\
**Post date:** [December 31, 2020, 4:16am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/44 "2020-12-31T04:16:58Z")

</div>

> [@Manan\_Mehta](#):
>
> `Your Twilio URL`

Where I can find:

- Your Twilio URL
- Twilio phone number
- Your API Code

😕

---

<div class="post-metadata">

**Author:** ![Lucas\_Pires](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lucas_pires/32/64863_2.png) [@Lucas\_Pires](https://community.glideapps.com/u/Lucas_Pires)\
**Post date:** [December 31, 2020, 4:30am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/45 "2020-12-31T04:30:51Z")

</div>

Now I realized we have only wizards here & me, a mere mortal 🤣

---

<div class="post-metadata">

**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\
**Post date:** [December 31, 2020, 4:40am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/46 "2020-12-31T04:40:14Z")

</div>

Twilio URL:  
[https://api.twilio.com/2010-04-01/Accounts/YOURACCOUNTSID/Messages.json](https://api.twilio.com/2010-04-01/Accounts/YOURACCOUNTSID/Messages.json)

Twilio Phone number is the number you must have bought on your Twilio account to send the SMS

API Code is: `YOURACCOUNTSID:YOURAUTHTOKEN`

You will find everything in your Twilio dashboard

---

<div class="post-metadata">

**Author:** ![wjcv06](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wjcv06/32/12747_2.png) [@wjcv06](https://community.glideapps.com/u/wjcv06)\
**Post date:** [December 31, 2020, 4:42am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/47 "2020-12-31T04:42:01Z")

</div>

Thank you so much!

---

<div class="post-metadata">

**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\
**Post date:** [December 31, 2020, 4:44am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/48 "2020-12-31T04:44:16Z")

</div>

A script to move rows from one sheet to another based on certain conditions. I run this trigger every 24 hours to get rid of past data and save it as a backup so my apps are always easier and quicker to load.

```
function moveRows() {

var ss = SpreadsheetApp.getActive();

    var source = ss.getSheetByName('Sheet1');

    var val = source.getDataRange().getValues();

    var PDate = new Date(new Date().setHours(0,0,0,0))

    for (var row = val.length - 1; row >= 0; --row) {

        Logger.log("Check: "+ val[row][1]+'----'+ PDate)

        if ((val[row][42] == 'Completed' || val[row][42] == 'Cancelled') && val[row][1] <= PDate && val[row][39] == 3) {

            ss.getSheetByName('Closed')

                .appendRow(val[row]);

            source.deleteRow(parseInt(row) + 1);

  } 

 } 

}
```

---

<div class="post-metadata">

**Author:** ![Manan\_Mehta](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/manan_mehta/32/13957_2.png) [@Manan\_Mehta](https://community.glideapps.com/u/Manan_Mehta)\
**Post date:** [December 31, 2020, 4:46am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/49 "2020-12-31T04:46:45Z")

</div>

I have seen your wizardry with CSS and your dope designs so let’s not get there 😂

---

<div class="post-metadata">

**Author:** ![Lucas\_Pires](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lucas_pires/32/64863_2.png) [@Lucas\_Pires](https://community.glideapps.com/u/Lucas_Pires)\
**Post date:** [December 31, 2020, 4:48am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/50 "2020-12-31T04:48:54Z")

</div>

![](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/e/eed9e7f9e64ac0efd13ca95161ed5301b5dab438.gif)

---

<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:** [December 31, 2020, 6:59am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/51 "2020-12-31T06:59:54Z")

</div>

You are the design wizard 🙂

---

<div class="post-metadata">

**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\
**Post date:** [December 31, 2020, 7:53am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/52 "2020-12-31T07:53:44Z")

</div>

And you are the Script wizard 😄

---

<div class="post-metadata">

**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\
**Post date:** [December 31, 2020, 5:13pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/53 "2020-12-31T17:13:28Z")

</div>

Question about the “pretty” function. I get this error message…

TypeError: Cannot read property ‘getBandings’ of null

More than getting it to work I’d like to understand the problem. As far as I can tell this means I haven’t declared the variable I am trying to get the value from?

Just a regular sized brain 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:** [December 31, 2020, 5:32pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/54 "2020-12-31T17:32:08Z")

</div>

How are you calling the function?  
It should be something like `format_sheet('Sheet 1')`

I actually use this within a wrapper function, something like this:

```
function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  format_sheet(sheetname);
}

```

That’s a simple example, normally I’ll have a an if/then/else block that first checks the sheetname, and then only calls the `format_sheet()` function for selected sheets.

That was the long answer.

The short answer is that error probably indicates that you didn’t give it a sheet name 🙂

> [@Eric\_Penn](#):
>
> More than getting it to work I’d like to understand the problem. As far as I can tell this means I haven’t declared the variable I am trying to get the value from?

Sorry, just realised that I didn’t address this question directly.  
Yes, that’s correct. `sheet` is null (I assume because of the above), and so when you try and do anything with it (`getBandings()`), that error is thrown.

---

<div class="post-metadata">

**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\
**Post date:** [December 31, 2020, 5:37pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/55 "2020-12-31T17:37:02Z")

</div>

Can you paste the script so we can take a look at it?

---

<div class="post-metadata">

**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\
**Post date:** [December 31, 2020, 6:02pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/56 "2020-12-31T18:02:06Z")

</div>

Thanks for the quick replies all.

The original script by @Darren_Murphy

> **Summary**
>
> function format\_sheet(sheetname) {  
> var ss = SpreadsheetApp.getActiveSpreadsheet();  
> var sheet = ss.getSheetByName(sheetname);  
> sheet.getBandings().forEach(function (banding) {  
> banding.remove();  
> });  
> var range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());  
> range.applyRowBanding(SpreadsheetApp.BandingTheme.BLUE);  
> range.setBorder(true, true, true, true, null, null, null, null);  
> range.setBorder(null, null, null, null, true, true, null, SpreadsheetApp.BorderStyle.DOTTED);  
> }

Modified by me

> **Summary**
>
> function format\_sheet(Database) {  
> var ss = SpreadsheetApp.getActiveSpreadsheet();  
> var sheet = ss.getSheetByName(Database);  
> sheet.getBandings().forEach(function (banding) {  
> banding.remove();  
> });  
> var range = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());  
> range.applyRowBanding(SpreadsheetApp.BandingTheme.BLUE);  
> range.setBorder(true, true, true, true, null, null, null, null);  
> range.setBorder(null, null, null, null, true, true, null, SpreadsheetApp.BorderStyle.DOTTED);  
> }

---

<div class="post-metadata">

**Author:** ![Eric\_Penn](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/eric_penn/32/73888_2.png) [@Eric\_Penn](https://community.glideapps.com/u/Eric_Penn)\
**Post date:** [December 31, 2020, 6:07pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/57 "2020-12-31T18:07:43Z")

</div>

I forgot the ‘…’

Edit: Is working! I’m adding it to a wrapper function like you suggested. Thx 👍

---

<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:** [December 31, 2020, 6:08pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/58 "2020-12-31T18:08:12Z")

</div>

If you’re running that as a stand-alone script, you can remove Database from line 1, and Database in line 3 needs to be enclosed in quotes

---

<div class="post-metadata">

**Author:** ![Rosewebstudio](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/rosewebstudio/32/12261_2.png) [@Rosewebstudio](https://community.glideapps.com/u/Rosewebstudio)\
**Post date:** [December 31, 2020, 6:21pm UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/59 "2020-12-31T18:21:08Z")

</div>

Wooo that’s flashy. Twilio, expensive? Couldn’t make sense of their pricing.

---

<div class="post-metadata">

**Author:** ![gp9293](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gp9293/32/16743_2.png) [@gp9293](https://community.glideapps.com/u/gp9293)\
**Post date:** [January 2, 2021, 11:23am UTC](https://community.glideapps.com/t/scripts-scripts-scripts/18965/60 "2021-01-02T11:23:43Z")

</div>

Twilio is a great service. Less than a penny per SMS. Worth the effort to learn and integrate.

[Previous page](https://community.glideapps.com/t/scripts-scripts-scripts/18965.md?page=2)

[Next page](https://community.glideapps.com/t/scripts-scripts-scripts/18965.md?page=4)