CODEWITHSUNDEEP
arraysoptimizationgoogle-apps-scriptgoogle-sheets
Fletcher Taylor
Fletcher Taylor

Reputation: 3

Slow Google Script - difficulties batching information into an array

I've been struggling with a little project of mine for a while now, and was looking for some assistance. The key issue I believe is simply me not being familiar with array script language and how to approach this. I've tried a few things after researching on here a bit and reading through the Best Practices section, but haven't been able to get it functioning adequately.

My script needs to be able to collect 200 rows x 200 columns of data from a spreadsheet, and depending on the number within each cell, it needs to select the corresponding number of columns next to that number and colour them in.

This was really simple to do with my basic programming knowledge, by just getting it to select each cell, check the number, select that range with an offset and then change the colour and move onto the next cell, however my code is incredibly slow because it does everything within the sheet without batching the data, and can't complete the full range within Google Script's time allowance. Any assistance on speeding it up would be greatly appreciated, as I haven't been able to get this working using arrays.

Here's the code I'm working with currently:

function CreateCalendar() {

  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName('Sheet2');
  var selection=ss.getRange("Sheet2!H2:FC140");
  var columns=selection.getNumColumns();
  var rows=selection.getNumRows();

  for (var column=1; column < columns; column++) {

    for (var row=1; row < rows; row++) { 
      var cell=selection.getCell(row,column);
      var cellvalue=cell.getValue();
        if (cellvalue >= 1) {
        var range=cell.offset(0,0,1,cellvalue);
        range.setBackground("blue");
      }
        else {;}  
    }
  }
}

Here's a public spreadsheet with confidential info removed and the sheet I'm targeting is Sheet2. Any assistance I could get on this would be greatly appreciated! Thanks

https://docs.google.com/spreadsheets/d/1Oe0aacfSBMmHpZvGPmjay5Q1bqBebnGQV4xlsK8juxk/edit#gid=0

Upvotes: 0

Views: 231

Answers (2)

Dean Ransevycz
Dean Ransevycz

Reputation: 953

You need to get rid of the repeated calls to range.getValue(). You can get all of the values for the range in one call & then iterate over that array in-script.

For your script it would look something like this:

function CreateCalendar() {

  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName('Sheet2');
  var selection=ss.getRange("Sheet2!H1:FC140"); // <= include header, but we'll skip it when we get to stepping over the array
  var values = selection.getValues(); // <= get all of the values now in one call
  for (var r=1; r < values.length; r++) {
    for (var c=0; c < values[r].length; c++) { 
      if (values[r][c] >= 1) {
        var range=sheet.getRange(r+1, c+8, 1, values[r][c]); // r+1 & c+8 because of array offsets
        range.setBackground("blue");
      }
        else {;}  
    }
  }
}

Take a look at Google's documentation: range.GetValues() https://developers.google.com/apps-script/reference/spreadsheet/range#getValues()

Upvotes: 1

Tanaike
Tanaike

Reputation: 201358

How about following sample script? If this is not your expectation, I would like to modify this script.

Sample script :

function CreateCalendar() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=ss.getSheetByName('Sheet2');
  var data = sheet.getRange("H2:FC140").getValues();
  data.forEach(function(e1, i1){
    e1.forEach(function(e2, i2){
      if (e2 >= 1) {
        sheet.getRange(i1+2, i2+8).offset(0,0,1,e2).setBackground("blue");
      }
    })
  })
}

Result (sample) :

enter image description here

If I misunderstand your question, I'm sorry.

Upvotes: 0

Related Questions