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
*
Multiplycell_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 :- Parse each cell.
- This can be seen at
c1
in the script.
- This can be seen at
- Create each cell using parsed cells.
- This can be seen at
c2
in the script.
- This can be seen at
- Create output cells.
- This can be seen at
c3
in the script.
- This can be seen at
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 :
Output :
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(',');
/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
const masterJoinFixed = array2d => recursiveFunc_(array2d).flat(3);
const recursiveFunc_ = arrOrString =>
Array.isArray(arrOrString)
? arrOrString.map(recursiveFunc_)
: String(arrOrString).split(',');
console.info(masterJoinFixed([['1,2,3'],[4],[5]]))
<!-- https://meta.stackoverflow.com/a/375985/ --> <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>
Related Topics
How to Execute a Function After Another One
Read Lines of a Txt File and Organize in a Json File
Using Html5/Javascript to Generate and Save a File
How to Apply CSS to Parent Using Child Selector - With CSS or Angular 2
Set Values in Input Type Date and Time in JavaScript
How to Use Aria-Expanded="True" to Change a CSS Property
How to Submit an HTML Form Without Redirection
Jquery Validate Phone Number With With Regex
Pass Input Tag Value to as Href Parameter
Multiple Key Names, Same Pair Value
Show/Hide Select Options Based on Value of Another Select Using Javascript/Jquery
Save Image File from Image Url JavaScript HTML
Angular Conditional Readonly/Disable on Input Fields
How to Check If a Cookie Exists
How to Mock Private Functions With Jest
Vue.Js - How to Call Method from Another Component