Scripts, scripts, scripts!

Yes, there is a different way, whether its better or not is up to you. I would do a for loop. When I get home I will take a look and see if I can do any better than you have already done. Your script is clean and easy to understand, I perform those actions differently thats all.

Thanks again for taking a look.

I continue to be amazed at this community and the support here. I’ve managed to build my first app (Ever!) using the Glide Library and help from this forum.

Cheers!

2 Likes

That’s great to hear man. Congrats!

2 Likes

Did you get the kinks worked out?

getLastRow is a Sheet method, if it’s operating on ‘null’ that probably means that the following line failed to retrieve a valid sheet object:

Whenever I’m trying to debug misbehaving functions, I find that console.log() becomes one of my best friends.

3 Likes

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?