Sheets - Output 2d array to sheet with optional fill mismatched array length function
Fri Jul 09 2021 02:04:07 GMT+0000 (Coordinated Universal Time)
Saved by
@dsottimano
/**
* 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;
};
content_copyCOPY
Comments