Examples of the Range class and triggers

10Range class & 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);
}

Impossibile richiamare il costruttore sovraccaricato forSpreadsheet con i parametri (null) perché c'è più di una firma
//EXAMPLE 2 - Set up onFormSubmit trigger
function example2Trigger() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ScriptApp.newTrigger("example2")
.forSpreadsheet(ss)
.onFormSubmit()
.create();
}