Google Apps Script Quickstart Code
Examples
Create a Spreadsheet with rows and columns values from a loop 1
Use array data to create a spreadsheet and populate the values 3
Apps Script UrlFetchApp Get URL Data and output to Web App 3
Populate Spreadsheet data from Web API 4
How to send data to an endpoint UrlFetchApp POST JSON 6
Sending Emails with MailApp Class using Google Apps Script 7
Advanced options for send emails with MailApp 8
How to check your daily quota of remaining emails to send in MailApp with
Apps Script 9
JSON output from Object Data into a web app URL 9
Create an Object using Sheet Data 10
Sheet Data as JSON Object connect with JavaScript output into a web page
11
Create a Spreadsheet with rows and columns
values from a loop
Create a sheet, with cell, row and column counters to populate the data. Create a
folder, or select a folder to move the spreadsheet file into.
1
Laurence Svekis - https://basescripts.com/
function maker1() {
//const id = '1Q7******v6IPjVU1P';
//const folder = DriveApp.getFolderById(id);
const folder = DriveApp.createFolder('New One');
const sheet = SpreadsheetApp.create('new sheet 2',5,3);
let cell = 1;
for(let row=0;row<5;row++){
const holder = [];
for(let col=0;col<3;col++){
holder.push(`Cell ${cell} Col ${col+1} Row ${row+1}`);
cell++;
}
sheet.appendRow(holder);
}
Logger.log(sheet.getUrl());
const sheetid = sheet.getId();
const file = DriveApp.getFileById(sheetid);
file.moveTo(folder);
}
2
Laurence Svekis - https://basescripts.com/
Use array data to create a spreadsheet and
populate the values
function maker2(){
const sheet = SpreadsheetApp.create('data',3,2);
sheet.appendRow(['one','two']);
sheet.appendRow(['three','four']);
sheet.appendRow(['five','six']);
}
Apps Script UrlFetchApp Get URL Data and
output to Web App
Fetch in the Class UrlFetchApp can be used to request a response from URLs.
Webapp with output from Google.com website source code.
3
Laurence Svekis - https://basescripts.com/
function doGet(){
const html = getData();
//return ContentService.createTextOutput(html);
return HtmlService.createHtmlOutput(html);
}
function getData(){
const url = 'http://www.google.com';
const response = UrlFetchApp.fetch(url);
return response.getContentText();
}
Populate Spreadsheet data from Web API
Connect to a web API and return the JSON data to Google Apps Script. Use the
JSON data to return object values that can then be used to populate into a
spreadsheet.
4
Laurence Svekis - https://basescripts.com/
function getUsers(){
const results = 10;
const url = 'https://randomuser.me/api/?results='+results;
const res = UrlFetchApp.fetch(url);
//Logger.log(res.getContentText());
const json = JSON.parse(res.getContentText());
//Logger.log(json.results);
const sheet = SpreadsheetApp.create('users');
sheet.appendRow(['First','Last','Country','Email']);
json.results.forEach(item =>{
const u = item.name;
const l = item.location;
sheet.appendRow([u.first,u.last,l.country,item.email]);
const user = `${u.title} ${u.first} ${u.last}`;
Logger.log(user);
})
}
5
Laurence Svekis - https://basescripts.com/
How to send data to an endpoint UrlFetchApp
POST JSON
Use of https://httpbin.org/#/HTTP_Methods/get_get and post to test fetch request.
function sender1(){
const url = 'https://httpbin.org/post';
const vals = {
'first' : 'Laurence',
'last' : 'Svekis',
'id' : 100
}
const strVals = JSON.stringify(vals);
const opts = {
'method' : 'post',
'contentType' : 'application/json',
'payload' : strVals
}
const rep = UrlFetchApp.fetch(url,opts);
const data = rep.getContentText();
const obj = JSON.parse(data);
6
Laurence Svekis - https://basescripts.com/
Logger.log(obj.json);
const sheet = SpreadsheetApp.create('JSON');
sheet.appendRow(['First','Last','ID']);
sheet.appendRow([obj.json.first,obj.json.last,obj.json.id]);
}
Sending Emails with MailApp Class using
Google Apps Script
Create a file as a blob, then attach the blob as a PDF in an email.
function creator1(){
const html = '<h1>Laurence Svekis</h1>';
const blob = Utilities.newBlob(html,'text/plain','newfile.txt');
const email = Session.getActiveUser().getEmail();
MailApp.sendEmail(email,'Check it out','Hello There',{
name : 'My File maker',
attachments : [blob.getAs(MimeType.PDF)]
});
}
7
Laurence Svekis - https://basescripts.com/
Advanced options for send emails with
MailApp
Send emails to multiple recipients with a comma separated string containing the
emails.
function creator2(){
const email = Session.getActiveUser().getEmail();
const html = '<h1>Laurence Svekis</h1>';
const emails =
'gapps******+1@gmail.com,gap******es+2@gmail.com,ga******es+3@gmail.c
om';
MailApp.sendEmail({
name : 'Laurence',
to : emails,
cc : email,
bcc : email,
replyTo : 'LaurenceSvekis******@basescripts.com',
subject : 'Subject',
htmlBody: html
});
}
8
Laurence Svekis - https://basescripts.com/
How to check your daily quota of remaining
emails to send in MailApp with Apps Script
function checker1(){
const val = MailApp.getRemainingDailyQuota();
Logger.log(val);
}
JSON output from Object Data into a web app
URL
function makerObj(){
const obj = [
{
first : 'Laurence',
last : 'Svekis',
id : 500
},
{
first : 'Jane',
last : 'Doe',
9
Laurence Svekis - https://basescripts.com/
id : 5
}
];
return obj;
}
function doGet(){
const obj = makerObj();
const output = JSON.stringify(obj);
return
ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JS
ON);
}
Create an Object using Sheet Data
First Last Country Email
Van der
Fiona Kwast Netherlands fiona.vanderkwast@example.com
Cosimo Schweikert Germany cosimo.schweikert@example.com
Lea Ginnish Canada lea.ginnish@example.com
10
Laurence Svekis - https://basescripts.com/
Lucas Anderson New Zealand lucas.anderson@example.com
Mustafa Türkdoğan Turkey mustafa.turkdogan@example.com
Ava Graves Ireland ava.graves@example.com
Ernest Masson Switzerland ernest.masson@example.com
Thibault Muller France thibault.muller@example.com
Gloria Carmona Spain gloria.carmona@example.com
Odila Caldeira Brazil odila.caldeira@example.com
function makeObj(rows,headings){
return rows.map(function(row){
const tempObj = {};
headings.forEach((heading,index)=>{
heading = heading.toLowerCase();
heading = heading.replace(/\s/g, '');
tempObj[heading] = row[index];
})
return tempObj;
})
}
Sheet Data as JSON Object connect with
JavaScript output into a web page
HTML
<!DOCTYPE html>
<html>
<head>
<title>Sheet Data</title>
</head>
<body>
11
Laurence Svekis - https://basescripts.com/
<div class="output">Click</div>
<script src="app.js"></script>
</body>
</html>
JavaScript
const url = 'https://script.google.com/macros/s/AK******/exec';
const output = document.querySelector('.output');
output.onclick = ()=>{
fetch(url)
.then(res => res.json())
.then(data =>{
maker(data);
})
}
function maker(data){
const main = document.createElement('div');
output.append(main);
data.forEach(user=>{
const div = document.createElement('div');
div.innerHTML = `${user.email} ${user.first} ${user.last}
${user.country}`;
main.append(div);
})
}
Apps Script
function sheetData(){
const id = '1JZA6Qi******ZHJ_eA';
const ss = SpreadsheetApp.openById(id);
12
Laurence Svekis - https://basescripts.com/
const sheet = ss.getSheetByName('data');
const data = sheet.getDataRange().getValues();
const headings = data[0];
const rows = data.slice(1);
return (makeObj(rows,headings));
}
function makeObj(rows,headings){
return rows.map(function(row){
const tempObj = {};
headings.forEach((heading,index)=>{
heading = heading.toLowerCase();
heading = heading.replace(/\s/g, '');
tempObj[heading] = row[index];
})
return tempObj;
})
}
function doGet(){
const obj = sheetData();
const output = JSON.stringify(obj);
return
ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JS
ON);
}
13
Laurence Svekis - https://basescripts.com/
