Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Examples of the Range class and triggers
//EXAMPLE 1 - Open sheet, highlight a certain cell and clear its content
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName("eg1");
var cell = sheet1.getRange("B4");
cell.activate();
cell.clearContent();
}
//EXAMPLE 2 - Set up onFormSubmit trigger
function example2Trigger() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ScriptApp.newTrigger("example2")
.forSpreadsheet(ss)
.onFormSubmit()
.create();
}
//EXAMPLE 2 - Add formula to last row, which is triggered when a form is submitted
function example2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet2 = ss.getSheetByName("eg2");
var lastRow = sheet2.getLastRow();
sheet2.getRange(lastRow,4).setFormulaR1C1("=R[0]C[-1]-R[0]C[-2]");
}
//EXAMPLE 3 - Set up onFormSubmit trigger
function example3Trigger() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ScriptApp.newTrigger("example3")
.forSpreadsheet(ss)
.onFormSubmit()
.create();
}
//EXAMPLE 3 - Add formula, add formatting when a form is submitted
function example3() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet3 = ss.getSheetByName("eg3");
var lastRow = sheet3.getLastRow();
sheet3.getRange(lastRow,4).setFormulaR1C1("=R[0]C[-1]-R[0]C[-2]");
var times = sheet3.getRange(lastRow,2,1,3);
times.setNumberFormat("HH:mm");
var data = sheet3.getDataRange();
data.setHorizontalAlignment("center");
data.setBorder(true, true, true, true, true, true);
sheet3.getRange(lastRow,1).setNumberFormat("DD/MM/YYYY");
}
//EXAMPLE4 - Change status to red or green
//NB: This example won't work as the next example also uses onEdit
//Comment out lines 72 to 87, to see this one working
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = e.range;
var column = range.getColumn();
var row = range.getRow();
var status = range.getValue();
if(column === 3 && row > 1 && row < 6 && status === "Closed"){
range.setBackground("#66BB6A");
}
else if(column === 3 && row > 1 && row < 6 && status === "Open"){
range.setBackground("#EF5350");
}
}
//EXAMPLE5 - Change row to red or green depending on status
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = e.range;
var column = range.getColumn();
var row = range.getRow();
var status = range.getValue();
if(column === 3 && row > 1 && status === "Closed"){
var rowToColor = range.offset(0, -2, 1, 3);
rowToColor.setBackground("#66BB6A");
}
else if(column === 3 && row > 1 && status === "Open"){
var rowToColor = range.offset(0, -2, 1, 3);
rowToColor.setBackground("#EF5350");
}
else if(column === 3 && row > 1){
var rowToColor = range.offset(0, -2, 1, 3);
rowToColor.setBackground("#FFFFFF");
}
}
//EXAMPLE 6 - Sort table by average mark and then by student's name
function example6() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet6 = ss.getSheetByName("eg6");
var range = sheet6.getDataRange();
range.sort([{column: 4, ascending: false}, {column: 1, ascending: true}]);
}
//EXAMPLE 7 - Copy part of a table to a new sheet
function example7() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet7 = ss.getSheetByName("eg7");
var range = sheet7.getRange(1, 1, 13, 3);
var checkSheet = ss.getSheetByName("new");
if (checkSheet) {
ss.deleteSheet(checkSheet);
}
var newSheet = ss.insertSheet("new");
var newSheetRange = newSheet.getRange(1, 1, 13, 3);
range.copyTo(newSheetRange);
newSheet.autoResizeColumn(1);
newSheet.setColumnWidth(3, 400);
newSheet.getRange(1, 3, 100).setWrap(true);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment