Split some strings and pass them in two different columns.

Question

I've the following situation :

Sheet 1 (input) :

www.url1.com?somestuff
www.url2.com?somestuff
www.url3.com?somestuff
www.url4.com?somestuff

Sheet 2 (expected output):

Col1          Col2
www.url1.com  ?somestuff
www.url2.com  ?somestuff
www.url3.com  ?somestuff
www.url4.com  ?somestuff

Here is what I've done until now :

function testwoD() {
var input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Raw_data");
var output = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet11");
var row_count = input.getLastRow()
var col_count = input.getLastColumn();

raw_data = input.getRange(1, 1,row_count,col_count).getValues()

tempArr = [] // or new Array
for (var i = 0; i < row_count; i++) { 
    tempArr.push(raw_data[i][6].split("?")[0]);
    tempArr.push(raw_data[i][6].split("?")[1]);
}

var toAddArray = [];
for (i = 0; i < tempArr.length; ++i){
    toAddArray.push([tempArr[i]]);
}

Logger.log(tempArr)
output.getRange(1, 1,730,1).setValues(toAddArray);
}

And here is the result I have on Sheet2 :

www.url1.com
?somestuff
www.url2.com
?somestuff
www.url3.com
?somestuff
www.url4.com
?somestuff

How can I reach the expected output ? I've read a lot of questions about transposing array but couldn't find the answer that could help me solve my issue.

Thanks !

Just curious why you don't use =split('?') in an arrayformula? — Brian
– Brian, Commented Apr 29, 2017 at 0:27

Jack Brown · Accepted Answer · 2017-04-28 23:40:41Z

Instead of push and concat you can do this

tempArr = [] // or new Array
for (var i = 0; i < row_count; i++) { 
    tempArr[i] = []
    tempArr[i][0] = raw_data[i][6].split("?")[0];
    tempArr[i][1] = raw_data[i][6].split("?")[1];
}

or better still you just push the whole split array.

for (var i = 0; i < row_count; i++) { 
    tempArr.push(raw_data[i][6].split("?"))
}

The basic idea is to get the two columns in the array per row index. So when you do setValues it writes in two corresponding columns

Final code:

function testwoD() {
var input = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Raw_data");
var output = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet11");
var row_count = input.getLastRow()
var col_count = input.getLastColumn();

raw_data = input.getRange(1, 1,row_count,col_count).getValues()

tempArr = [] // or new Array
for (var i = 0; i < row_count; i++) { 
    tempArr[i] = []
    tempArr[i][0] = raw_data[i][6].split("?")[0];
    tempArr[i][1] = raw_data[i][6].split("?")[1];
}
// Not sure what this code is suppose to achieve? hence removed it
/*var toAddArray = [];
for (i = 0; i < tempArr.length; ++i){
    toAddArray.push([tempArr[i]]);
}*/

Logger.log(tempArr)
// You can use setValues and get numof rows and columns using array length
output.getRange(1, 1,tempArr.length,tempArr[0].length).setValues(tempArr);
}

Hope that helps

Collectives™ on Stack Overflow

Split some strings and pass them in two different columns.

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related