Converting 1D array with multiple values into 2D array in google script without Loop in Loop

Question

I'm trying to shorten my script time, as it is reaching the apps script time limit too often (1800s). Therefore, I'm trying to reduce the number of loops the script is performing. The script goal is to collect all Montecarlo Revenue analysis results, and yo do so it iterates 1000 I (E.g. 1000) times. Each iterations collects the following data points: Total Revenue, # of logos and the same per month. I've managed to do that through creating a Loop in a Loop (Loopin I for the Montecarlo iterations, and looping J through each data point) and creating a 2D array that later I post in my sheet using SetValues.

  for (var I=0; I < Runs; I++) {
    MCOutput[I] = new Array(DataLength);
    MCOutput[I][0] = I+1;
    sheet.getRange(6,18).setValue((I+1)/Runs);
    for (var J=1; J<DataLength; J++) {
    MCOutput[I][J]=sheet.getRange(5,J+StartCol).getValue();
    }
  sheet.getRange(Row,StartCol,MCOutput.length,MCOutput[0].length).setValues(MCOutput);

My goal is to reduce the running time, by looping only once and collecting all the data through GetValues. I've managed to do so, but I can't find a way to set these values to a 2D array in the sheet. I'm getting the following error:

Exception: The number of columns in the data does not match the number of columns in the range. The data has 1 but the range has 21.

Here is the script for it:

var MCOutput = [];
for (var I=0; I < Runs; I++) {
  MCOutput[I] = new Array(DataLength);
  sheet.getRange(6,18).setValue((I+1)/Runs);
  sheet.getRange(5,StartCol).setValue(I+1);
  MCOutput[I]=sheet.getRange(5,StartCol,1,DataLength).getValues();
}
sheet.getRange(Row,StartCol,I,DataLength).setValues(MCOutput);

I wasn't able to solve it through map or splice, I guess it is because my 1D array has rows and columns of data.

Related/Possible duplicate: stackoverflow.com/questions/63720612/… — TheMaster
– TheMaster, Commented Aug 20, 2022 at 18:57

TheMaster · Accepted Answer · 2022-08-20 15:04:26Z

1

Here are some modification I would suggest.

new Array() can slow down a script and really isn't needed here.
getValues() returns a 2D array so you need to get the first row getValues()[0] of it and push it into the MCOutput array.
sheet.getRange(6,18).setValue((I+1)/Runs); does absolutely nothing that I can see and multiple calls to setValue() can really slow down a script.
you can simple replace the value in MCOutput[I][0] = I+1;

// you are always getting the same row and StartCol or Datalength don't change
let values = sheet.getRange(5,StartCol,1,DataLength).getValues()[0]; 
let MCOutput = [];
for (let I=0; I < Runs; I++) {
  // assuming values is only javascript primitives a shallow copy will do
  MCOutput.push(values.map( value => value ));
  MCOutput[I][0] = I+1;
}
sheet.getRange(Row,StartCol,I,DataLength).setValues(MCOutput);

Reference:

edited Aug 20, 2022 at 15:04

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

answered Aug 20, 2022 at 14:32

TheWizEd

8,6542 gold badges14 silver badges23 bronze badges

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

8 Comments

Shuf Over a year ago

Thank you both for the help, it helped me to solve it! Few changes I made and a question: 1. changed let I = 0 to var I = 0to be able to use it out of the loop for 'setValues'. 2. pushed the 'let values' into the loop, as each repetition, it's a different data set.3. Added back the sheet.getRange(6,18).setValue((I+1)/Runs); as I need the script to write something every loop, to trigger the Rand() function in the sheet. I wonder if I can trigger it any other way without writing. Any suggestions?

TheWizEd Over a year ago

If values in row 5 are recalculated with each iteration of setValue((I+1)/Runs) then this answer is not correct. I would suggest you move all calculation inside the sciprt. I believe cached setValue(s) and getValue(s) are flushed when it changes from one to the other. But you may need to use SpreadsheetApp.flush() to insure the latest values are in the sheet ranges you are interested.

TheWizEd Over a year ago

Your OP appears benign because much of the process and variable are not exposed to us. If you need recalculation of the sheet to provide updated data to your script then I would consider doing everything either in sheet or in script.

Shuf Over a year ago

Thank you @TheWizEd, I appreciate the willingness to help. I'm afraid that doing all calculations in the script will just make it even slower, and reach the time limit every time. Should I add a link to the sheet and the whole script by editing my answer? P.S. I've tried SpreadsheetApp.flush(), but unfortunately, it does not trigger the Rand() function.

Shuf Over a year ago

Thank you both. I'll revisit my approach and see if I can create the full calculations in the script, before posting it. As I understood, my main issue with the speed is the writing back to the sheet every run of the loop, and I'll try to avoid it. Thanks so much for the help.

|

Collectives™ on Stack Overflow

Converting 1D array with multiple values into 2D array in google script without Loop in Loop

1 Answer 1

Reference:

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Reference:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related