Skip to content

Instantly share code, notes, and snippets.

@peterherrmann
peterherrmann / outerLoop.js
Last active February 28, 2023 12:38
outerLoop for Google Apps Script triggered functions is some boilerplate that encapsulates best practice ways to deal with running workloads that may take longer to process that the time available in an Apps Script triggered run.
//load configuration details and start logging - creates and sets up sheets the first time they are run
var CONFIG_SPREADSHEET_KEY = '<ssid_goes_here>';
var Config = SettingsManager.load(CONFIG_SPREADSHEET_KEY); //Add Mafviu9bMfg9xVu21LGfpWnHAGDwXQ1CH in Resources > Libraries
Logger = BetterLog.useSpreadsheet(Config['logSpreadsheetId'].value);//Add MYB7yzedMbnJaMKECt6Sm7FLDhaBgl_dE in Resources > Libraries
// trigger this function
function outerLoop() {
try {
// to calc elapsed time
var isOverMaxRuntime = false,
@peterherrmann
peterherrmann / gist:2836208
Created May 30, 2012 13:08
Spreadsheet Splitter - Google Apps Script
// Creates new spreadsheets based on a column containing email addresses
// in the starting spreadsheet.
// Each new spreadsheet will contain the full set of rows for that email address.
// It will then be shared with that email address and a link emailed.
var EMAIL_COLUMN = 2; //Use the number: A=1,B=2,Z=26 etc
var ALSO_SHARE_WITH = ""; // Add any additional addresses here. Separated multiples with commas.
var NUM_HEADER_ROWS = 1; // The number of header rows.
// Do not change anything below this line
// 24 Nov 2010 - test mode works correctly after Google changes (session broke after Browser.msgBox)
@peterherrmann
peterherrmann / gist:2707115
Created May 16, 2012 03:32
Spreadsheet filter - view "my data" filtered to UiApp table
// Spreadsheet Filter - a script that runs as a service and allows users
// to view just their data from a spreadsheet and optionally* others' data
// too as granted by the spreadsheet owner.
//
// * requires a sheet named "Additional Access" with 2 columns:
// "User" (1st column heading) Email of a user who has been delegated access
// to others' data;
// "Additional access" (2nd column heading) Comma separated list of emails of
// users for which the first user will be given access. Use * to indicate any user.
// Version: 11 March 2011
@peterherrmann
peterherrmann / gist:2700284
Last active July 6, 2024 10:27
GASRetry - Exponential backoff JavaScript implementation for Google Apps Script (Library project key: "MGJu3PS2ZYnANtJ9kyn2vnlLDhaBgl_dE")
/**
* Invokes a function, performing up to 5 retries with exponential backoff.
* Retries with delays of approximately 1, 2, 4, 8 then 16 seconds for a total of
* about 32 seconds before it gives up and rethrows the last error.
* See: https://developers.google.com/google-apps/documents-list/#implementing_exponential_backoff
<h3>Examples:</h3>
<pre>//Calls an anonymous function that concatenates a greeting with the current Apps user's email
var example1 = GASRetry.call(function(){return "Hello, " + Session.getActiveUser().getEmail();});
</pre><pre>//Calls an existing function
var example2 = GASRetry.call(myFunction);