Google Apps Script Coding Examples FREE PDF Downloadable GUIDE

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.

function maker1() {
//const id = ‘1Q7v6IPjVU1P’;
//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);
}

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.

​​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.

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

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);
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 = ‘

Laurence Svekis

‘;
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)]
});
}

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 = ‘

Laurence Svekis

‘;
const emails = ‘gapps+1@gmail.com,gapes+2@gmail.com,gaes+3@gmail.com’; MailApp.sendEmail({ name : ‘Laurence’, to : emails, cc : email, bcc : email, replyTo : ‘LaurenceSvekis@basescripts.com’,
subject : ‘Subject’,
htmlBody: html
});
}

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’,
id : 5
}
];
return obj;
}

function doGet(){
const obj = makerObj();
const output = JSON.stringify(obj);
return ContentService.createTextOutput(output).setMimeType(ContentService.MimeType.JSON);
}

Create an Object using Sheet Data

First
Last
Country
Email
Fiona
Van der Kwast
Netherlands
fiona.vanderkwast@example.com
Cosimo
Schweikert
Germany
cosimo.schweikert@example.com
Lea
Ginnish
Canada
lea.ginnish@example.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


Sheet Data

Click

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 = ‘1JZA6QiZHJ_eA’;
const ss = SpreadsheetApp.openById(id);
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.JSON);
}

Leave a Comment