outputSheet snippet

snippet.js

/**
 * 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);
}