Skip to content

Instantly share code, notes, and snippets.

View andrewroberts's full-sized avatar
💭
Cranking out the code... 👨‍💻

Andrew Roberts andrewroberts

💭
Cranking out the code... 👨‍💻
View GitHub Profile
@andrewroberts
andrewroberts / FuzzySet.gs
Created September 8, 2020 09:03
FuzzySet (https://glench.github.io/fuzzyset.js/) with a bit of re-packaging for use with Google Apps Script
/**
* @param {string} value - value to test
* @param {array} valuesToTest
*
* @return the best match in the test array
*/
function getBestMatch(value, valuesToTest) {
var results = {}
@andrewroberts
andrewroberts / exportSpreadsheet.gs
Last active July 2, 2024 08:44 — forked from Spencer-Easton/exportSpreadsheet.gs
Example on how to export a Google sheet to various formats, includes most PDF options. Updated from comments in [Spencer Easton's Gist](https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70)
function exportSpreadsheet() {
//All requests must include id in the path and a format parameter
//https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export
//FORMATS WITH NO ADDITIONAL OPTIONS
//format=xlsx //excel
//format=ods //Open Document Spreadsheet
//format=zip //html zipped
@andrewroberts
andrewroberts / formatString.js
Created February 14, 2020 07:26
JavaScript function to format various value types as a string.
function testNumber() {
var value = 1234.56789
var a = format(value, {numberOfDecimalPlaces: 2})
debugger
}
function testDate() {
var value = new Date()
var a = format(value)
@andrewroberts
andrewroberts / generateRandomString.gs
Last active July 26, 2020 16:01
Generate a random string of length n
function generateRandomString(n) {
var chars = ['a', 'b','c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9'];
chars.push('A', 'B', 'C', 'D', 'E', 'F','G','H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
var randomString = '';
for (i=0; i < n; i++) {
r = Math.random();
r = r * 61;
r = Math.round(r);
randomString = randomString + chars[r];
}
@andrewroberts
andrewroberts / Properties_.gs
Created July 16, 2018 09:42
Google Apps Script snippet to manage and store persistent state. Uses both Firebase (FirebaseApp library), CacheService and BBLog for logging.
// 34567890123456789012345678901234567890123456789012345678901234567890123456789
// JSHint - TODO
/* jshint asi: true */
(function() {"use strict"})()
// Properties_.gs
// ==============
//
@andrewroberts
andrewroberts / createPDF_filename.gs
Last active September 1, 2024 15:40
This is a Google Apps Script that creates a PDF file using a Google Doc template and the values in a Google Spreadsheet. It is based on www.andrewroberts.net/2014/10/google-apps-script-create-pdf/ but allows placeholders to be used in the filename.
// dev: andrewroberts.net
// Replace this with ID of your template document.
var TEMPLATE_ID = ''
// var TEMPLATE_ID = '1wtGEp27HNEVwImeh2as7bRNw-tO4HkwPGcAsTrSNTPc' // Demo template
// Demo script - http://bit.ly/createPDF
// You can specify a name for the new PDF file here, or leave empty to use the
// name of the template. Placeholders can also be placed in here.
@andrewroberts
andrewroberts / API_.gs
Created September 25, 2017 11:02
Google Apps Script snippet for accessing the Quick Books Online API
// 34567890123456789012345678901234567890123456789012345678901234567890123456789
// JSHint - TODO
/* jshint asi: true */
(function() {"use strict"})()
// API_.gs
// =======
//
@andrewroberts
andrewroberts / fillInHtmlTemplate.gs
Created September 17, 2017 10:02
Google Apps Script function to replace placeholder values in an HTML template (from a draft GMail for example)
/**
* For each placeholder - "{{[placeholder]}}", strip out any HTML and replace
* it with the appropriate key value. An example use of this could be using the
* HTML from a draft GMail as a template.
*
* E.g. If the obect were:
*
* {PlaceHolder1: newValue}
*
* In the template "{{PlaceHolder1}}" would be replaced with "newValue" even
@andrewroberts
andrewroberts / PDFCreator_SendMultiplePDFs.gs
Last active November 15, 2020 19:43
Google Apps Script for creating and emailing multiple PDFs using data from a Google Sheet and a Google doc template.
/*
PDF Creator - Send multiple PDFs
================================
On selecting a number of contiguous rows and clicking "Create PDFs > Create PDFs
for selected rows" this script constructs a PDF for each selected row in the
attached GSheet.
The value in the "File Name" column is used to name the file and - if there is a
@andrewroberts
andrewroberts / StackTraces-Logging-GAS.md
Last active July 7, 2017 11:58 — forked from hlecuanda/StackTraces-Logging-GAS.md
Example using Stackdriver logging for debugging a custom function for sheets in Google Apps Script. / Writing a Caching custom function

Logging custom functions using stackdriver in google apps script

Recently, console logging became available for Google Apps Script projects. It used to be impossible to use the Log service that comes with the GAS runtime, but now all you need to do is throw an exception. Exceptions get logged in stackdriver logging and when enabled, unhandled exceptions will not stop your script execution. This adds up to nearly 0 lag if you are using this feature (?) by purposely throwing exceptions, and you can get creative with your error message to avoid having to expand stackdriver's log messages (which are pretty comprehensive stacktraces!)

Setup