Split strings in multiple columns into multiple rows in Sheets

I'm trying to re-use a script that worked well in my sheet in the past, but I got an error when the script was triggered to run. The error message was "Custom function parameters too large".

const result = range =>
  range.flatMap(([a, b, ...v]) => {
    const { vv, len } = v.reduce((o, c) => {
      const t = typeof c != "string" ? c.toString().split(", ") : c.split(", ");
      o.vv.push(t);
      o.len = o.len < t.length ? t.length : o.len;
      return o;
    }, { vv: [], len: 0 });
    const temp = vv.map(e => e.concat(Array(len - e.length).fill("")));
    return temp[0].map((_, i) => [...(i == 0 ? [a,] : Array(1).fill("")), b, ...temp.map(r => isNaN(r[i].trim()) ? r[i].trim() : r[i].trim() && Number(r[i]))]);
  });

  // Credits: Tanaike (https://stackoverflow.com/questions/70078195/how-to-split-strings-in-multiple-columns-into-multiple-rows/70081177?noredirect=1#comment123884616_70081177)

It seems like the error happens because I have too many data rows (the data rows are Google form responses and I have close to 20000 responses so far). Does anyone has any suggestion on what I could try to work around the issue? I'm actually unfamiliar with Google Apps Script and would appreciate it if anyone has any tips, thanks!

CodePudding user response：

In your situation, I thought that the values might be required to be put using the Spreadsheet service (SpreadsheetApp) and/or Sheets API instead of the custom function. So, how about the following sample script?

Pattern 1:

In this pattern, the values are put using the Spreadsheet service (SpreadsheetApp). Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet names of source and destination sheets. And, run sample1 with the script editor.

function sample1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName("Sheet1"); // Please set the source sheet name.
  const dstSheet = ss.getSheetByName("Sheet2"); // Please set the destination sheet name.
  const values = srcSheet.getDataRange().getValues();
  const res = values.flatMap(([a, b, c, d, e, f, g, ...v]) => {
    const { vv, len } = v.reduce((o, c) => {
      const t = typeof c != "string" ? c.toString().split(",") : c.split(",");
      o.vv.push(t);
      o.len = o.len < t.length ? t.length : o.len;
      return o;
    }, { vv: [], len: 0 });
    const temp = vv.map(e => e.concat(Array(len - e.length).fill("")));
    return temp[0].map((_, i) => [...(i == 0 ? [a, b, c, d] : Array(4).fill("")), e, f, g, ...temp.map(r => isNaN(r[i].trim()) ? r[i].trim() : r[i].trim() && Number(r[i]))]);
  });
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

Pattern 2:

In this pattern, the values are put using Sheets API. Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet names of source and destination sheets. And, please enable Sheets API at Advanced Google services. run sample1 with the script editor.

function sample2() {
  const srcSheet = "Sheet1"; // Please set the source sheet name.
  const dstSheet = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const values = Sheets.Spreadsheets.Values.get(ssId, srcSheet).values;
  const res = values.flatMap(([a, b, c, d, e, f, g, ...v]) => {
    const { vv, len } = v.reduce((o, c) => {
      const t = typeof c != "string" ? c.toString().split(",") : c.split(",");
      o.vv.push(t);
      o.len = o.len < t.length ? t.length : o.len;
      return o;
    }, { vv: [], len: 0 });
    const temp = vv.map(e => e.concat(Array(len - e.length).fill("")));
    return temp[0].map((_, i) => [...(i == 0 ? [a, b, c, d] : Array(4).fill("")), e, f, g, ...temp.map(r => isNaN(r[i].trim()) ? r[i].trim() : r[i].trim() && Number(r[i]))]);
  });
  Sheets.Spreadsheets.Values.update({ values: res }, ssId, dstSheet, { valueInputOption: "USER_ENTERED" });
}

Pattern 1:

Pattern 2:

References: