These utilities are grouped into related files, for simpler copy & paste to your scripts.
A couple of helper functions to convert to & from A1 notation.
Convert a cell reference from A1Notation to 0-based indices (for arrays) or 1-based indices (for Spreadsheet Service methods).
Return a 0-based array index corresponding to a spreadsheet column label, as in A1 notation.
Return a 0-based array index corresponding to a spreadsheet row number, as in A1 notation. Almost pointless, really, but maintains symmetry with colA1ToIndex().
Provides a collection of handy-dandy utilities and helpers for dealing with CSV files in Google apps-script.
Populates a sheet with contents read from a CSV file located in the user's GDrive. If either parameter is not provided, the function will open inputBoxes to obtain them interactively.
Automatically detects tab or comma delimited input.
Adapted from Tutorial: Interacting With Your Docs List.
8 Apr 2015 - DocsList deprecated
Throws Error
- "No Input File" if input CSV not found.
Returns an array with contents read from a CSV file located in the user's GDrive.
Adapted from Tutorial: Interacting With Your Docs List.
8 Apr 2015 - DocsList deprecated
Throws Error
- "No filename" if input parameter missing.
- "No Input File" if input CSV not found.
Populate an Array with the contents of the given Sheet. This represents the rectangular region that encompases all populated cells in the given sheet. Two modes of operation are supported. When called from a spreadsheet script, the active spreadsheet is used. Otherwise, it is expected that a spreadsheet is provided by its id. If the sheet name does not exist in the spreadsheet, an empty 2-D Array is returned.
Populate a sheet with contents of the given 2D Array.
This Custom Function will join multiple ranges into a new table at a given anchor point. To operate as a custom function callable from a sheet, the parameters are string expressions of ranges, in a1Notation. (It could easily be refactored to deal directly with Range objects.)
The "Anchor" for the new range is expected to be a cell. One or more ranges of any size may be joined - each will be positioned directly below the previous.
VJoin("D1","A1:B")
All of columns A & B duplicated in columns D & E
VJoin("Sheet2!A1","Sheet1!C9:E10","Sheet1!A14:B15");
Two different ranges in Sheet 1 joined and copied to Sheet 2.
Addresses the bug reported as Google Apps Script Issue 4355, wherein blank responses are skipped in the event object passed to trigger functions.
Force blank reponses into event object's values property, so that the value's index correctly reflects the question order. (With "new Sheets" + "new Forms", blank responses are skipped in the event object.)
function onFormSubmit(e) {
fixFormEvent( e );
...
}
Using the Advanced Drive Service, this function will create a copy of a Google Spreadsheet, formatted as an Excel .xlsx
file. See this StackOverflow Question for more info.
Toaster is a utility Class that wraps the Spreadsheet.toast()
method with an object, and provides additional support for multi-line toast
messages in Google Spreadsheeets. See this StackOverflow Question for more info.
Usage:
function testToaster() {
var myToast = new Toaster( "Msg Line 1\nMsg Line 2\nAnother Line that's longer\nFollowed by one more.",
"Multi-line toast", 5 );
myToast.display();
}
Displays:
Multi-line toast
Msg Line 1
Msg Line 2
Another Line that's longer
Followed by one more.
Thank you for your work :)