Instantly share code, notes, and snippets.
Created
May 7, 2017 13:52
-
Save barrieroberts/eca98a641734f6ae68618d8124dd3240 to your computer and use it in GitHub Desktop.
Apps Script Basics - if, prompt, menu, & onOpen trigger (2)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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"); | |
} | |
} | |
//Set background to red if attendance is less than 80% | |
//Otherwise set it to green | |
function example2() { | |
var ss = SpreadsheetApp.getActiveSheet(); | |
var attendance = ss.getRange("B3").getValue(); | |
if (attendance < 0.8) { | |
ss.getRange("B3").setBackground("red"); | |
} | |
else { | |
ss.getRange("B3").setBackground("green"); | |
} | |
} | |
//Set background to red if attendance is less than 70% | |
//Set background to yellow if attendance is 70-80% | |
//Set background to green if attendance is 80% or more | |
function example3() { | |
var ss = SpreadsheetApp.getActiveSheet(); | |
var attendance = ss.getRange("B3").getValue(); | |
if (attendance < 0.7) { | |
ss.getRange("B3").setBackground("red"); | |
} | |
else if (attendance < 0.8) { | |
ss.getRange("B3").setBackground("yellow"); | |
} | |
else { | |
ss.getRange("B3").setBackground("green"); | |
} | |
} | |
//Set background of name to red if | |
//attendance is less than 80% OR exam is less than 70% | |
function example4() { | |
var ss = SpreadsheetApp.getActiveSheet(); | |
var attendance = ss.getRange("E3").getValue(); | |
var exam = ss.getRange("F3").getValue(); | |
if (attendance < 0.8 || exam < 0.7) { | |
ss.getRange("D3").setBackground("red"); | |
} | |
else { | |
ss.getRange("D3").setBackground("green"); | |
} | |
} | |
//Set background of name to green if | |
//attendance is 80% or more AND exam is 70% or more | |
function example5() { | |
var ss = SpreadsheetApp.getActiveSheet(); | |
var attendance = ss.getRange("E3").getValue(); | |
var exam = ss.getRange("F3").getValue(); | |
if (attendance >= 0.8 && exam >= 0.7) { | |
ss.getRange("D3").setBackground("green"); | |
} | |
else { | |
ss.getRange("D3").setBackground("red"); | |
} | |
} | |
//Open sheet from name in cell B4 and paste student's data | |
function example6() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet1 = ss.getSheetByName('Sheet1'); | |
var name = sheet1.getRange("B5").getValue(); | |
if(name === "John") { | |
var figures = sheet1.getRange("A7:E9").getValues(); | |
} | |
else if(name === "Ringo") { | |
var figures = sheet1.getRange("A10:E12").getValues(); | |
} | |
else if(name === "Paul") { | |
var figures = sheet1.getRange("A13:E15").getValues(); | |
} | |
else if(name === "George") { | |
var figures = sheet1.getRange("A16:E18").getValues(); | |
} | |
var studentSheet = ss.getSheetByName(name).activate(); | |
studentSheet.getRange("A1:E3").setValues(figures); | |
} | |
//Return back to Sheet1 | |
function example6b() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet1 = ss.getSheetByName('Sheet1').activate(); | |
} | |
//Ask which sheet user wants to open and then open it | |
function example7() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var ui = SpreadsheetApp.getUi(); | |
var response = ui.prompt('Enter name:'); | |
if (response.getSelectedButton() === ui.Button.OK) { | |
var name = response.getResponseText(); | |
ss.getSheetByName(name).activate(); | |
} else { | |
} | |
} | |
//Using ternary operator-If less than 80% set background red, otherwise green | |
function example8() { | |
var ss = SpreadsheetApp.getActiveSheet(); | |
var cellB3 = ss.getRange("B3"); | |
var attendance = cellB3.getValue(); | |
(attendance < 0.8) ? cellB3.setBackground("red"):cellB3.setBackground("green"); | |
} | |
//Set background colour for all students' attendance figures | |
function example9() { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet1 = ss.getSheetByName('Sheet1'); | |
var attendanceFigures = sheet1.getRange("I3:I6").getValues(); | |
for (i=0;i<attendanceFigures.length;i++){ | |
var r = i+3; | |
if (attendanceFigures[i] < 0.7) { | |
sheet1.getRange(r,9).setBackground("red"); | |
} | |
else if (attendanceFigures[i] < 0.8) { | |
sheet1.getRange(r,9).setBackground("yellow"); | |
} | |
else { | |
sheet1.getRange(r,9).setBackground("green"); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,I'm first time user to google script and have tried example7 which is getting me error "cannot call SpreadsheetApp.getUi() from this context"
here is my code
function prompt()
{
var ss =SpreadsheetApp.openById('XXXXXX');
var sh = ss.getSheetByName('Sheet');
var spreadsheet=ss.getActiveSheet();
Logger.log(sh)
var ui=SpreadsheetApp.getUi();
var response=ui.prompt('Enter name:');
}