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

Question

I'm trying to split a string into separate cells, but the order of substrings will be different to how it appears in the string originally. Not all parts of the string are necessary and the length of each substring isn't static. I managed to achieve something workable on a separate project where the substring lengths were static using lastIndexOf but it was incredibly clunky.

I'm hoping that someone could point me to a better way of approaching the problem.

Each string of data will look like this:

01|020202|03|0404040404|05|0606060606|

My previous code was equivalent to this:

function splitString() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Data");
 var input = sheet.getRange(2,4).getValue(); 

 var data1 = input.substring(input.lastIndexOf("!")-5,input.lastIndexOf("|")-1);
 var data2 = input.substring(input.lastIndexOf("|")-10, data1);

 // etc.

 sheet.getRange(3,2).setValue(data1);
 sheet.getRange(4,2).setValue(data2);

 // etc.

}

Can anybody suggest a better method for doing this? Any help would be much appreciated.

Wicket · Accepted Answer · 2018-05-02 17:21:36Z

1

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

answered May 2, 2018 at 17:21

Wicket

39.4k9 gold badges81 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheAddonDepot · Accepted Answer · 2018-05-02 15:42:48Z

1

Look thru the various string API methods for Javascript on MDN. There should be one to split a string into an array using a delimiter (the pipe "|" character in your case). Then checkout the setValues method of the Range class which allows you to set a series of cells using an array with a single method call.

answered May 2, 2018 at 15:42

TheAddonDepot

9,0653 gold badges25 silver badges36 bronze badges

Comments

John More · Accepted Answer · 2018-05-02 16:49:41Z

1

function splitString() {
  var theSplitString 
  ="01|020202|03|0404040404|05|0606060606|".split("|");
  Logger.log(theSplitString);
  // the result is an array [01, 020202, 03, 0404040404, 05, 0606060606, ]
}`

Then as suggested use the range.setValues

answered May 2, 2018 at 16:49

John More

214 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related