Method for Splitting String into Separate Cells in Sheets/App Script

Method for splitting string into separate cells in Sheets / App Script

It looks that you need that each element be on the same column on differents rows. Use String.prototype.split() then convert the resulting array into a 2D array.

The following example, for simplicity built on "pure" JavaScript, use forEach but there are several other ways to do the same.

var s = '01|020202|03|0404040404|05|0606060606|';var t = s.split('|');var output = [];t.forEach(function(q){ output.push([q]);});console.info(output);

Spreadsheet - Google App Script [string split function]

Your code should definitely work, I just ran this with a breakpoint on Logger.log(array1); The debugger shows it as an array, and the log logs it as: [A, B, C, D]. Note, that to get the output you wanted I had to add a space to the split to get: string1.split(", ");

function myFunction() {
var array1 = splitTest();
Logger.log(array1);
}

function splitTest() {
var array1 = [{}];
var string1 = "A, B, C, D";

array1 = string1.split(", ");
return array1
}

Split strings in multiple columns into multiple rows in Sheets - Google Apps Script

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" });
}

References:

  • setValues(values)
  • Method: spreadsheets.values.update

In google sheets, can you split string into substrings, perform calculations on substrings and concatenate results in different cell

A1:

20/40/40

B1:(cell_number)

5

C1:

=ARRAYFORMULA(JOIN("/",B1*(SPLIT(A1,"/")/{100*4,100*4,100*9})))
  • SPLIT A1 by / to get numbers. 20|40|40
  • / Divide it by a array of 100. 20/400|40/400|40/900
  • * Multiply cell_number B1. 5*20/400|5*40/400|5*40/900
  • JOIN the result back with /

Split multi-line cells into different rows

How about this sample script? I think that there are some methods for achieving what you want. So please think of this as one of them. In order to achieve your situation, I separated the script to 3 parts.

Flow :

  1. Parse each cell.

    • This can be seen at c1 in the script.
  2. Create each cell using parsed cells.

    • This can be seen at c2 in the script.
  3. Create output cells.

    • This can be seen at c3 in the script.

Sample script :

// Main of this script.
function result(range) {
var output = [];
for (var i in range) {
var celLen = 1;
var c1 = range[i].map(function(e, i){
var cell = e.toString().split("\n"); // Modified
var len = cell.length;
if (len == 1) {
return cell[0];
} else if (len > 1) {
celLen = celLen > len ? celLen : len;
var t2 = [];
for (var k=0; k<cell.length; k++) {
t2.push(cell[k]);
}
return t2;
}
});
var c2 = c1.map(function(e, i){
var r = [];
if (!Array.isArray(e)) {
for (var k=0; k<celLen; k++) {
r.push(e);
}
} else {
for (var k in e) {
r.push(e[k]);
}
if (e.length < celLen) {
for (var m=0; m<celLen - e.length; m++) {
r.push("");
}
}
}
return r;
});
var c3 = c2[0].map(function(e, i){return c2.map(function(f, j){return c2[j][i]})});
Array.prototype.push.apply(output, c3);
}
return output;
}

// For testing this script.
function main() {
var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues();
var r = result(data);
ss.getRange(ss.getLastRow() + 1, 1, r.length, r[0].length).setValues(r);
}
Sample result :

Input :

enter image description here

Output :

enter image description here

Note :

  • In this sample script, you can increase the columns freely.
  • In order to parse each value of cells, \n is used.
  • I'm not sure about other patterns for your situation. So I prepared this sample script for supposing some situations. Please confirm the sample result.

If I misunderstand your question, please tell me. I would like to modify it.

How can I split strings in Google Apps Script to avoid "Text result of JOIN is longer than the limit of 50000 characters"?

Issue:

  • The values look like [["employee1,employee2"],["employee3"]] and not like [["employee1","employee2"],["employee3"]]. In the former case, they're strings. In the latter case, they're arrays.(They're not!)

Solution:

  • Use recursion to recurse over all elements

  • Split the string elements and

  • Flatten the resulting array

Sample script:

/**
* Simulates TRANSPOSE(SPLIT(JOIN(",",array),","))
* @param {Object[][]} array2d The two dimensional array passsed from Google sheet
* @returns {String[]} 1D array - 1D is enough for custom function
* @customfunction
*/
const masterJoinFixed = array2d => recursiveFunc_(array2d).flat(3);

/**
* Recursive helper function to split all elements
* of a multi dimensional array by comma `,`
* @param {Object[]|String} arrOrString
* @returns {Object[]}
* @private
*/
const recursiveFunc_ = arrOrString =>
Array.isArray(arrOrString)
? arrOrString.map(recursiveFunc_)
: String(arrOrString).split(',');