Skip to content

Instantly share code, notes, and snippets.

@barrieroberts
Created May 7, 2017 13:52
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save barrieroberts/eca98a641734f6ae68618d8124dd3240 to your computer and use it in GitHub Desktop.
Save barrieroberts/eca98a641734f6ae68618d8124dd3240 to your computer and use it in GitHub Desktop.
Apps Script Basics - if, prompt, menu, & onOpen trigger (2)
//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");
}
}
}
@vvundela08
Copy link

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:');
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment