barrieroberts

function onOpen() {
   SpreadsheetApp.getUi()
       .createMenu('New menu')
       .addItem('Example 1', 'example1')
       .addItem('Example 2', 'example2')
       .addItem('Example 3', 'example3')
       .addItem('Example 4', 'example4')
       .addItem('Example 5', 'example5')
       .addItem('Example 6', 'example6')
       .addItem('Return to Sheet1', 'example6b')

barrieroberts

//Set background to red if attendance is less than 80%
function example1() {
  var ss = SpreadsheetApp.getActiveSheet();
  var attendance = ss.getRange("B3").getValue();
  
  if (attendance < 0.8) {
    ss.getRange("B3").setBackground("red");
  }
}

barrieroberts

//Create menu to run examples from
function onOpen(){
  SpreadsheetApp.getUi()
  .createMenu("Examples")
  .addItem("example 1", "example1")
  .addItem("example 2", "example2")
  .addItem("example 3", "example3")
  .addItem("example 4", "example4")
  .addItem("example 5", "example5")
  .addItem("example 6", "example6")

barrieroberts

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Reports')
    .addItem('Make pdfs', 'createReports')
    .addItem('Email reports', 'sendEmails')
    .addToUi();
}
function createReports() {
  //Get data from sheet and remove header
  const ss = SpreadsheetApp.getActiveSpreadsheet();

barrieroberts

//Copy a spreadsheet, rename the new one using original name
//Display a toast message once the process has finished
function example1() {
  var ss1 = SpreadsheetApp.getActiveSpreadsheet();
  var ss1Name = ss1.getName();
  ss1.copy(ss1Name + "-example1");
  ss1.toast("Spreadsheet copied & named", "Finished", 5);
}

//Add an editor and viewers to the new spreadsheet

barrieroberts

//Makes a copy of a hidden master sheet and renames it with the teacher's name
function example1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssId = ss.getId();
  var destination = SpreadsheetApp.openById(ssId);
  var master = ss.getSheetByName("master");
  var newSheet = master.copyTo(destination);
  
  newSheet.showSheet();
  var teachersName = ss.getSheetByName("name").getRange("A1").getValue();

barrieroberts

//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

barrieroberts

function createFormFromData() {
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  //Get data, number of questions and options info
  var data = sh.getDataRange().getValues();
  var numOfOptions = data.length-3;
  var numOfQs = data[0].length;

  //Get questions
  var questions = sh.getRange(2, 2, 1, numOfQs).getValues();

barrieroberts

function createForm() {
  var form = FormApp.create("New form");
  var formQ1 = form.addMultipleChoiceItem();
  formQ1.setTitle('Where do you want to go on holiday?');
  formQ1.setChoiceValues(['Seville', 'London']);
}

function createFormFromData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("newQ");
  var question = ss.getRange(2, 1, 1, 1).getValue();

barrieroberts

//Create new form - Global variable so it can be seen by all functions
const FORM = FormApp.create("Questionnaire");

function makeQuestionnaire() {
  //Get data and last row from spreadsheet
  const sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const data = sh.getDataRange().getValues();

  //Loop through each question and check what question type it is
  data.forEach(checkQuestionType)