Google App Script Cheat Sheet

A quick reference to Google App Script.

Created on: 2019-10-16

I have been using Google App Script for quick hack and automation for sometime now. Though it would be easy to record the reusable parts.

Google App Script is based on JavaScript so many JavaScript syntax works here as well. Take a look at JavaScript Cheat Sheet

sha512 hash of a string

to make a sha512 hash of a string:

Utilities.base64EncodeWebSafe(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_512, 'string'));

https://stackoverflow.com/a/47636721/5350059

filter range where string exist

to filter range where string exist:

sheet.getRange(coupon_column).getValues().filter(String)

we can find the length with .length method:

sheet.getRange(coupon_column).getValues().filter(String).length

receive GET and POST request

to receive GET and POST request:

function doGet(e){
    return main(e)
}

function doPost(e) {
    return ContentService.createTextOutput('Error!!! POST method not allowed.').setMimeType(ContentService.MimeType.TEXT);
}

return data as json

to return data as json:

return ContentService.createTextOutput(JSON.stringify({"key":"value"})).setMimeType(ContentService.MimeType.JSON;

return data as text

to return data as text:

return ContentService.createTextOutput('Error!!! POST method not allowed.').setMimeType(ContentService.MimeType.TEXT);

retrieve post data

to retrieve post data 1:

var post_data = e.postData.contents // where `e` is event

append row to Google Sheet

to append row to a Google Sheet 2:

sheet.appendRow([data,data]) //must be a array

get sheet by index

to get sheet by index 3:

var sheet = sheet.getSheets()[index];

get sheet by name

to get sheet by name 4:

var sheet = sheet.getSheetByName($sheet_name);

parse json from string

to parse json from string:

var json_parsed = JSON.parse(response)

sent request

to sent request 5:

var url = "https://example.com"

var data = {"key1":"value","key2":"value"}

options = {
        "method": "post",
        "headers": {},
        "payload": data};

var response = UrlFetchApp.fetch(url, options);

retrieve URL encoded params

to retrieve URL encoded params:

var params = e.parameter //where e is event

get sheet by sheet id

to get sheet by sheet idi 6:

var sheet_id = "1vq-rgLqnWQm-WD1eDwdALCNPrY86g27AT--QJ6CIxPY";
var open_sheet = SpreadsheetApp.openById(sheet_id);

print log

to print log 7:

Logger.log($string_in_quote_or_var);

set value to cell

to set value to cell 8:

sheet.getRange("B2").setValue(100);

set values to range

to set values to a range 9:

var values = [
  [ "2.000", "1,000,000", "$2.99" ]
  ];

var range = sheet.getRange("B2:D2");
range.setValues(values);

sent mail using Gmail

to sent mail using Gmail 10:

var name = "User";
var var1 = "value";
var email = "user@mail.com"
var subject = "Test Mail"

var htmlOutput = HtmlService.createHtmlOutputFromFile('mail'); // make sure you have a HTML mail template named 'mail' [try https://plnkr.co/edit]
var message = htmlOutput.getContent()
message = message.replace("%name", name); // make sure you have a variable with name '%name'
message = message.replace("%var1", var1);  // make sure you have a variable with name '%var1'

MailApp.sendEmail(email, subject, message, {htmlBody : message});

generate random alphanumeric string

to generate random alphanumeric string of twelve char:

number = Math.random().toString(36).slice(2,12).toUpperCase()

get range with variable cell number

to get range with variable cell number:

var cell = "C"+$var_num+":C"
var values = info_sheet.getRange(cell).getValues()

list file name, id and size in a folder

to list file name, id and size in a folder 11:

var folder = DriveApp.getFolderById($folder_id);

var files = folder.getFiles();

  while (files.hasNext()){
        file = files.next();
        file_name = file.getName()
        file_id = file.getId()
        file_size = file.getSize()
}

automatically redirecting to a page

to automatically redirect to a page 12:

function doGet() {
        return HtmlService.createHtmlOutput(
                "<script>window.top.location.href='+"url"+';</script>"
        );
}

save a html file as pdf

to save a html file as pdf 13:

var folder = DriveApp.getFolderById($folder_id);
var blob = HtmlService.createHtmlOutputFromFile($html_file_name);
blob = blob.getBlob();
var pdf = blob.getAs("application/pdf");
folder.createFile(pdf).setName("$file_name.pdf")