Sheets - Output 2d array to sheet with optional fill mismatched array length function

PHOTO EMBED

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