CODEWITHSUNDEEP
arraysgoogle-apps-scriptmultidimensional-arraytimemontecarlo
Shuf
Shuf

Reputation: 1

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

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.

Upvotes: 0

Views: 1259

Answers (1)

TheWizEd
TheWizEd

Reputation: 8606

Here are some modification I would suggest.

  1. new Array() can slow down a script and really isn't needed here.
  2. getValues() returns a 2D array so you need to get the first row getValues()[0] of it and push it into the MCOutput array.
  3. 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.
  4. 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:

Upvotes: 1

Related Questions