0

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.

David Scholz
  • 8,421
  • 12
  • 19
  • 34
Shuf
  • 1
  • 2
  • Related/Possible duplicate: https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept – TheMaster Aug 20 '22 at 18:57

1 Answers1

1

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:

TheMaster
  • 45,448
  • 6
  • 62
  • 85
TheWizEd
  • 7,517
  • 2
  • 11
  • 19
  • 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 = 0`to 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? – Shuf Aug 20 '22 at 16:43
  • 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 Aug 20 '22 at 17:12
  • 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. – TheWizEd Aug 20 '22 at 17:15
  • 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 Aug 20 '22 at 17:26
  • TheWizEd No problem. @Shuf You should read the first link in the answer. Script isn't slowing you down. It's all the repeated calls. – TheMaster Aug 20 '22 at 18:54
  • I think you should open a new question on how to integrate your spreadsheet formulas and your script. Post a link to a sample spreadsheet that we can work with to improve the process. – TheWizEd Aug 20 '22 at 22:59
  • @Shuf If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Aug 21 '22 at 00:13
  • 1
    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. – Shuf Aug 21 '22 at 10:32