Split setValues() of array data into two sets of columns (e.g. B - H and AV - AX) - Google Spreadsheet

Question

I have a for loop that builds an array of data of size n rows by 10 columns.

var dupRows = crossoverDuplicates.getDataRange(); var dupNumRows = dupRows.getNumRows(); var dupValues = dupRows.getValues(); var retreiveNotFound = []; for (var c = 1; c < dupNumRows; c++) { var dupRow = dupValues[c]; if (dupRow[10] == "") { retreiveNotFound.push(dupValues[c]); } }

I then use setValues() to paste the array to the bottom of another sheet.

if (retreiveNotFound && retreiveNotFound.length) { crossover.getRange(lastRowCrossoverData + 1, 2, retreiveNotFound.length,10 ).setValues(retreiveNotFound); }

However, the columns on the destination sheet have now moved and I need to paste the first 7 columns of the array in B - H and the last 3 in AV - AX.

Alternatively, I could create two arrays, one with the first 7 columns and another with the last 3 and then paste the separately.

Unfortunately, I can't figure out how to do either.

I'm guessing this has a relatively simple solution, but I'm just not searching for the right key words. Thanks in advance for your help!

Yes. Thank you! I made a couple of minor edits to your code and it worked perfectly. For some reason my comment on the edits is not displaying and I apparently don't have a high enough reputation to vote up your answer, but I am truly grateful! — sandeaj
– sandeaj, Commented Nov 13, 2017 at 5:23

Alan Wells · Accepted Answer · 2017-11-14 14:59:39Z

Move Google spreadsheet columns into two separate places. Split Google spreadsheet columns up and put them into different columns or two different columns. This code gets data from one place, and then creates two separate arrays, and writes the data into two non-contiguous ranges.

function myFunction() { var dupRows = crossoverDuplicates.getDataRange(); var dupNumRows = dupRows.getNumRows(); var dupValues = dupRows.getValues(); var B_H_Array = []; var AV_AX_Array = []; var AV_AX_TempArray, B_X_TempArray, c, dupRow, thisRowsData;//Define variables //without assigning a value for (c = 1; c < dupNumRows; c++) { dupRow = dupValues[c]; if (dupRow[10] == "") { thisRowsData = dupValues[c]; B_X_TempArray = thisRowsData.slice(0,7);//Slice out the first elements AV_AX_TempArray = thisRowsData.slice(7,10);//Slice out last elements B_H_Array.push(B_X_TempArray); AV_AX_Array.push(AV_AX_TempArray); } } if (B_H_Array.length) { crossover.getRange(lastRowCrossoverData + 1, 2,B_H_Array.length, B_H_Array[0].length ) .setValues(B_H_Array); } if (AV_AX_Array.length) { crossover .getRange(lastRowCrossoverData + 1, 48,AV_AX_Array.length, AV_AX_Array[0].length ) .setValues(AV_AX_Array); } }

Collectives™ on Stack Overflow

Split setValues() of array data into two sets of columns (e.g. B - H and AV - AX) - Google Spreadsheet

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related