How to make simple multi-threading with google app scripts for google sheets

Question

I have a lot of google chart to update and I want to parallelize the processing of the for loop.

I've seen the code on this link : Threading in Google App Script but it's too complicated for I have to do. I do not need to get parallelize the code and get the data back.

 for(i=0;i<nbChart;i++)
  {
    Vmax=rangeMax[i*6];
    Vmin=rangeMinId[i*6];
    id=rangeMinId[(i*6)+1];
    var delta=(Vmax-Vmin)*0.1;
    Logger.log("Vmax="+Vmax+"Vmin="+Vmin+"id="+id+"i="+i);
    var chart = sheet.getCharts()[id];
    if(chart.getType()!="COLUMN")
    {
      Vmin-=delta
      Vmax=Number(Vmax)+(delta*1.5)//Number() function to avoid Vmax becoming a string for no reason
    }
    Logger.log("Vmax="+Vmax+"Vmin="+Vmin+"id="+id+"i="+i);
     chart = chart.modify()
      .setOption('vAxes', {0: {textStyle: {fontSize: 10}, titleTextStyle: {fontSize : 8}, viewWindow: {min: Vmin, max:Vmax}}})//adpative vaxis for AREA and COMBO
      .build();
    sheet.updateChart(chart);
  }

I just need to update my charts quickly because it's currently taking about 15 minutes to update for a single sheet.

Rafa Guillermo · Accepted Answer · 2019-10-11T12:06:53.970

At current you're calling sheet.getCharts() every time the loop runs which will drastically increase the exectution time. Putting the function call outside of the loop and then only refer to the chart by its ID inside would reduce the number of function calls in the loop:

var chart = sheet.getCharts();

for(i = 0; i < nbChart; i++) {
  Vmax = rangeMax[i * 6];
  Vmin = rangeMinId[i * 6];
  id = rangeMinId[(i * 6) + 1];
  var delta=(Vmax - Vmin) * 0.1;
  var currChart = chart[id];

  if(currChart.getType() != "COLUMN") {
    Vmin -= delta
    Vmax = Number(Vmax) + (delta * 1.5) //Number() function to avoid Vmax becoming a string for no reason
  }
  currChart = currChart.modify()
    .setOption('vAxes', {0: {textStyle: {fontSize: 10}, titleTextStyle: {fontSize : 8}, viewWindow: {min: Vmin, max:Vmax}}}) //adpative vaxis for AREA and COMBO
    .build();
  sheet.updateChart(currChart);
}

As another solution, if the order in which you update the charts is not important, you could write a second script with the same loop structure but with a different looping condition. If you run two loops in two different scripts, one running for (i = 0; i < nbChart; i = i + 2) and the second running for (i = 1; i < nbChart; i = i + 2), you can then set up two installable triggers to run them both at the same time or on the same action which would also reduce oveall execution time.

Thanks very much I will try that. I was also going to do your second solution to reduce the time ! — LALLEMENT.E, Jul 12 '19 at 15:31
I followed your advice and I tried to create a function which create 2 instance of my function but the function doesn't seem to run at all. Should I create an other post or should I edit this one ? — LALLEMENT.E, Jul 15 '19 at 15:02
@LALLEMENT.E It would be better if you make a new question dedicated to the issue. — Rafa Guillermo, Jul 15 '19 at 15:05
I posted the complete solution on this link : https://stackoverflow.com/questions/57053706/how-to-create-trigger-to-run-multiple-function-in-parallel-passing-parameter/57071937#57071937 — LALLEMENT.E, Jul 18 '19 at 07:46

How to make simple multi-threading with google app scripts for google sheets

1 Answers1