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