Scripts, scripts, scripts!

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.

9 Likes

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();
   }
 }
   }

}
6 Likes

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:

8 Likes

Where I can find:

  • Your Twilio URL
  • Twilio phone number
  • Your API Code

:confused:

1 Like

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

9 Likes

Twilio URL:
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

2 Likes

Thank you so much!

1 Like

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);

  } 

 } 

}
10 Likes

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

6 Likes

4 Likes

You are the design wizard :slightly_smiling_face:

6 Likes

And you are the Script wizard :smile:

6 Likes

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 :wave:

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 :slightly_smiling_face:

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.

1 Like

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

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

1 Like

I forgot the ‘…’

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

1 Like

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

2 Likes

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

3 Likes

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

2 Likes