Scripts, scripts, scripts!

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

I found a service called clicksend last night. It seems to be a good alternative to Twilio.

Take a look :eyes: https://www.clicksend.com/us/pricing/us/#sms

7 Likes

Thanks @SantiagoPerez will take a look, always like seeing a new to play with

4 Likes