Documentation from Google:
https://developers.google.com/apps-script/reference/base/
Terminology:
Range --> a collection of cells
google spreadsheet script editor
1. Get a Spreadsheet
--> get active spread sheet
--> get by url
--> get by ID
2. Get a range
3. Permissions:
each JS module requires permission
Spreadsheet /
WARNING !!!!
do not share sheets that have triggers in them.. people who are editing can at the minimum exploit the permissions already assigned to the sheet.
3. Functions we wrote:
function logObject(object){
Logger.log(object);
}
function myFunction() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(2, 6).setValue("Shahbaz");
}
function copyFromSettingsToData() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Data");
var settingsSheet = ss.getSheetByName("Settings");
// copy from settings to data
valueToCopy = settingsSheet.getRange(1, 1).getValue();
dataSheet.getRange(2, 2).setValue(valueToCopy);
}
function sendEmail(personArray){
if(personArray[3] != "SENT"){
logObject(personArray);
// MailApp.sendEmail(personArray[1], "Hi !" + personArray[0], personArray[2]);
// personArray[3] = "SENT";
}
}
function sendEmailToUsers(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("Data");
var lastRow = dataSheet.getLastRow();
var lastColumn = dataSheet.getLastColumn();
var emailData = dataSheet.getRange(2, 1, lastRow-1,lastColumn).getValues();
// loop over list to send email
emailData.forEach(sendEmail);
}
function getHTMLBitcoinPrices(priceArray){
var htmlString = '<body>';
priceArray.forEach(function(priceObj){
// htmlString
htmlString+='<p>Coin Name: '+priceObj[1]+'</p>';
htmlString+= '<ul>';
htmlString+='<li>Price: $'+ priceObj[4] +'</li>';
htmlString+='</ul>';
});
htmlString+='</body>';
return htmlString;
}
function writeBitcoinToSheet(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var bitcoinSheet = ss.getSheetByName("bitcoinData");
outDataArray = [];
priceData = UrlFetchApp.fetch("https://api.coinmarketcap.com/v1/ticker/?limit=100");
// Logger.log(priceData);
priceArray = JSON.parse(priceData.getContentText());
// get object keys
keysArray = Object.keys(priceArray[0]);
// write title array
outDataArray.push(keysArray);
// write rest of the rows
priceArray.forEach(function(bitcoinPrice){
valueArray = [];
keysArray.forEach(function(key){
valueArray.push(bitcoinPrice[key]);
});
outDataArray.push(valueArray);
});
htmlString = getHTMLBitcoinPrices(outDataArray.slice(1,outDataArray.length));
MailApp.sendEmail('sanyamsmulay@gmail.com', 'bitcoin data', "some body", {htmlBody:htmlString});
// bitcoinSheet.getRange(1, 1, outDataArray.length, outDataArray[0].length).setValues(outDataArray);
}
function sendConfirmEmails(peopleData){
if(peopleData[3] != "SENT"){
MailApp.sendEmail(peopleData[2], "Shahbaz's Email Tut -- Submit Success", "Yaay ! you have registered");
peopleData[3] = "SENT";
}
}
function buildNSendTicket(sheet, ticketSheet, personDataArray, slotInfoArray){
// Logger.log(slotInfoArray);
var name = personDataArray[1];
var email = personDataArray[2]
var date = slotInfoArray[0];
var slotNo = slotInfoArray[1];
ticketSheet.getRange("I13").setValue(name);
ticketSheet.getRange("I17").setValue(date);
ticketSheet.getRange("I20").setValue(slotNo);
SpreadsheetApp.flush();
var url = Drive.Files.get(sheet.getId())
.exportLinks['application/pdf'];
url = url + '&size=letter' + //paper size
'&portrait=false' + //orientation, false for landscape
'&fitw=true' + //fit to width, false for actual size
'&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional
'&gridlines=false' + //false = hide gridlines
'&gid='+ ticketSheet.getSheetId() +
'&fzr=false'+ //do not repeat row headers (frozen rows) on each page
'&top_margin=0.30'+ //All four margins must be set!
'&bottom_margin=0.00 '+
'&left_margin=0.00'+
'&right_margin=0.00'+
'&horizontal_alignment=CENTER'+
'&vertical_alignment=CENTER';
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, { headers: {'Authorization': 'Bearer ' + token}});
var blob=response.getBlob();
blob.setName("Ticket for :" + name);
MailApp.sendEmail(email, "Your ticket to the workshop !!", "Please find attached, your ticket to the event", {attachments:[blob]});
}
function assignSlot(slotDataArray, personDataArray){
for(var i = 0; i < slotDataArray.length; i++){
if(slotDataArray[i][2] == "" ){
slotDataArray[i][2] = personDataArray[2];
MailApp.sendEmail(personDataArray[2], "Slot Assigned for Shaz's Workshop", "Date: "+
slotDataArray[i][0]+" Slot #:"+ slotDataArray[i][1]);
return {
personDataArray: personDataArray,
slotInfoArray: slotDataArray[i]
};
}
}
MailApp.sendEmail(personEmail, "Slot Not Assigned", "Sorry we could not assign a slot to you. Better luck next time.")
}
function sendEmailToFormRespondents(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responseSheet = ss.getSheetByName("ticketSubs");
var lastRow = responseSheet.getLastRow();
var lastColumn = responseSheet.getLastColumn();
var peopleDataRange = responseSheet.getRange(2, 1, lastRow-1, lastColumn);
var peopleDataArray = peopleDataRange.getValues();
var slotSheet = ss.getSheetByName("slotTimings");
var slotDataRange = slotSheet.getDataRange();
var slotDataArray = slotDataRange.getValues();
// Logger.log(slotDataArray);
// peopleDataArray.forEach(sendConfirmEmails);
// this is inefficient -- should write only statuses
// peopleDataRange.setValues(peopleDataArray);
var slotAssignRes = assignSlot(slotDataArray, peopleDataArray[peopleDataArray.length - 1]);
var ticketSheet = ss.getSheetByName("ticketUI");
// Logger.log(slotAssignRes.slotInfoArray);
buildNSendTicket(ss, ticketSheet, slotAssignRes.personDataArray,slotAssignRes.slotInfoArray);
// Logger.log("after assignment:");
// Logger.log(slotDataArray);
// Logger.log(slotDataRange);
slotDataRange.setValues(slotDataArray);
}
4. Functions are representative of what activities are possible with sheets
5. Form submission and associated triggers
6. Float an event form with limited slots
-- assign slots
-- send confirmation email
-- attach ticket
(all done in google sheets)