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 - [Link]
function maker1() {
//const id = '1Q7******v6IPjVU1P';
//const folder = [Link](id);
const folder = [Link]('New One');
const sheet = [Link]('new sheet 2',5,3);
let cell = 1;
for(let row=0;row<5;row++){
const holder = [];
for(let col=0;col<3;col++){
[Link](`Cell ${cell} Col ${col+1} Row ${row+1}`);
cell++;
}
[Link](holder);
}
[Link]([Link]());
const sheetid = [Link]();
const file = [Link](sheetid);
[Link](folder);
}
2
Laurence Svekis - [Link]
Use array data to create a spreadsheet and
populate the values
function maker2(){
const sheet = [Link]('data',3,2);
[Link](['one','two']);
[Link](['three','four']);
[Link](['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 [Link] website source code.
3
Laurence Svekis - [Link]
function doGet(){
const html = getData();
//return [Link](html);
return [Link](html);
}
function getData(){
const url = '[Link]
const response = [Link](url);
return [Link]();
}
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 - [Link]
function getUsers(){
const results = 10;
const url = '[Link]
const res = [Link](url);
//[Link]([Link]());
const json = [Link]([Link]());
//[Link]([Link]);
const sheet = [Link]('users');
[Link](['First','Last','Country','Email']);
[Link](item =>{
const u = [Link];
const l = [Link];
[Link]([[Link],[Link],[Link],[Link]]);
const user = `${[Link]} ${[Link]} ${[Link]}`;
[Link](user);
})
}
5
Laurence Svekis - [Link]
How to send data to an endpoint UrlFetchApp
POST JSON
Use of [Link] and post to test fetch request.
function sender1(){
const url = '[Link]
const vals = {
'first' : 'Laurence',
'last' : 'Svekis',
'id' : 100
}
const strVals = [Link](vals);
const opts = {
'method' : 'post',
'contentType' : 'application/json',
'payload' : strVals
}
const rep = [Link](url,opts);
const data = [Link]();
const obj = [Link](data);
6
Laurence Svekis - [Link]
[Link]([Link]);
const sheet = [Link]('JSON');
[Link](['First','Last','ID']);
[Link]([[Link],[Link],[Link]]);
}
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 = [Link](html,'text/plain','[Link]');
const email = [Link]().getEmail();
[Link](email,'Check it out','Hello There',{
name : 'My File maker',
attachments : [[Link]([Link])]
});
}
7
Laurence Svekis - [Link]
Advanced options for send emails with
MailApp
Send emails to multiple recipients with a comma separated string containing the
emails.
function creator2(){
const email = [Link]().getEmail();
const html = '<h1>Laurence Svekis</h1>';
const emails =
'gapps******+1@[Link],gap******es+2@[Link],ga******es+3@gmail.c
om';
[Link]({
name : 'Laurence',
to : emails,
cc : email,
bcc : email,
replyTo : 'LaurenceSvekis******@[Link]',
subject : 'Subject',
htmlBody: html
});
}
8
Laurence Svekis - [Link]
How to check your daily quota of remaining
emails to send in MailApp with Apps Script
function checker1(){
const val = [Link]();
[Link](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 - [Link]
id : 5
}
];
return obj;
}
function doGet(){
const obj = makerObj();
const output = [Link](obj);
return
[Link](output).setMimeType([Link]
ON);
}
Create an Object using Sheet Data
First Last Country Email
Van der
Fiona Kwast Netherlands [Link]@[Link]
Cosimo Schweikert Germany [Link]@[Link]
Lea Ginnish Canada [Link]@[Link]
10
Laurence Svekis - [Link]
Lucas Anderson New Zealand [Link]@[Link]
Mustafa Türkdoğan Turkey [Link]@[Link]
Ava Graves Ireland [Link]@[Link]
Ernest Masson Switzerland [Link]@[Link]
Thibault Muller France [Link]@[Link]
Gloria Carmona Spain [Link]@[Link]
Odila Caldeira Brazil [Link]@[Link]
function makeObj(rows,headings){
return [Link](function(row){
const tempObj = {};
[Link]((heading,index)=>{
heading = [Link]();
heading = [Link](/\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 - [Link]
<div class="output">Click</div>
<script src="[Link]"></script>
</body>
</html>
JavaScript
const url = '[Link]
const output = [Link]('.output');
[Link] = ()=>{
fetch(url)
.then(res => [Link]())
.then(data =>{
maker(data);
})
}
function maker(data){
const main = [Link]('div');
[Link](main);
[Link](user=>{
const div = [Link]('div');
[Link] = `${[Link]} ${[Link]} ${[Link]}
${[Link]}`;
[Link](div);
})
}
Apps Script
function sheetData(){
const id = '1JZA6Qi******ZHJ_eA';
const ss = [Link](id);
12
Laurence Svekis - [Link]
const sheet = [Link]('data');
const data = [Link]().getValues();
const headings = data[0];
const rows = [Link](1);
return (makeObj(rows,headings));
}
function makeObj(rows,headings){
return [Link](function(row){
const tempObj = {};
[Link]((heading,index)=>{
heading = [Link]();
heading = [Link](/\s/g, '');
tempObj[heading] = row[index];
})
return tempObj;
})
}
function doGet(){
const obj = sheetData();
const output = [Link](obj);
return
[Link](output).setMimeType([Link]
ON);
}
13
Laurence Svekis - [Link]