Skip to content

Instantly share code, notes, and snippets.

@brainysmurf
Last active Jul 9, 2021
Embed
What would you like to do?
outputSheet snippet
/**
* Append a 2d array to an existing sheet or can create a new sheet
* to match the largest row in the dataset.
* davidsottimano.com
* @param {array} data 2d array of data
* @param {string} sheetName (optional) The sheet you want to output to. If the sheet does not exist, this script will auto-create a new sheet with the specified sheet name. If the sheetName is not specified, a new sheet will be created
*/
function outputToSheet(data, sheetName=null) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
if (sheetName==null) sheetName = 'Default Sheet Name';
// Create a "map" (just internal memory), so I can use if there are any sheets that exists by that name
// if it's there, grab it, without having to grab it again. Saves a roundtrip call
const sheets = new Map(ss.getSheets().map(sheet => (sheet.getName(), sheet) ));
// ^--- if that seems a bit magical, what it's doing is creating an array of pairs (name, sheet), and the new Map
// is just a fancy way of converting an array of paired values into a "map" object (with .has and .get methods)
if (!sheets.has(sheetName)) {
// not there, so let's create it and store it in memory
const newSheet = ss.insertSheet(sheetName);
sheets.set(sheetName, newSheet);
}
// now we retrieve it from stored memory
const targetSheet = sheets.get(sheetName);
const lastRow = ss.getLastRow() + 1;
const lastCol = 1;
// find the width of the data by going through each row's length, and finding the biggest one
const width = Math.max(...data.map(row => row.length));
const height = data.length;
// this actually changes the data that was passed in … may not be desirable
// anyway, this will add nulls to the end of the array if mismatched
for (const row of data) {
row.length = width;
}
return targeteSheet.getRange(lastRow, lastCol, height, width).setValues(data);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment