/** * Outputs 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) { let ss = SpreadsheetApp.getActiveSpreadsheet(); let sheet = ss.getSheetByName(sheetName) == null ? ss.insertSheet(sheetName) : ss.getSheetByName(sheetName).activate(); let lastRow = ss.getLastRow() + 1; let lastCol = 1; /* if your 2d data array contains missing or mismatched element count run the following line along with the fillOutRange function included in this snippet data = fillOutRange(data, '') */ return sheet.getRange(lastRow, lastCol, data.length, data[0].length).setValues(data); } /** * Fills each row array to the right with selected value * to match the largest row in the dataset. * https://yagisanatode.com/2019/11/24/google-apps-script-filling-out-your-column-data-to-match-the-number-of-columns-in-a-range/ * @param {array} range: 2d array of data * @param {string} fillItem: (optional) String containg the value you want to add to fill out your array. * @returns 2d array with all rows of equal length. */ function fillOutRange(range, fillItem) { let fill = (fillItem === undefined) ? "" : fillItem; //Get the max row length out of all rows in range. let initialValue = 0; let maxRowLen = range.reduce(function (acc, cur) { return Math.max(acc, cur.length); }, initialValue); //Fill shorter rows to match max with selected value. let filled = range.map(row => { let dif = maxRowLen - row.length; if (dif > 0) { let arizzle = []; for (let i = 0; i < dif; i++) { arizzle[i] = fill }; row = row.concat(arizzle); } return row; }) return filled; };