CODEWITHSUNDEEP
algorithmperformancegoogle-apps-scriptgoogle-sheetstime
Killanight
Killanight

Reputation: 1

I need to find a faster solution to iterate rows in Google App Script

I'm trying to save some rows values for multiple columns on multiple tabs in GAS, but it's taking a lot of time and I'd like to find a faster way of doing this, if there's any.

A project e.g:'Project1' -as a key- has a value associated with it which corresponds to the column where it's stored, the tabs are 600+ iterations long.

this script opens up a tab called 'person1' at first and goes through all the rows for the column that corresponds to that project in 'projects' dictionary (it's the same format for every tab, but more projects will be added in the future)

right now i'm iterating through the 'members' dictionary (length=m), then through the projects dictionary (length=p) and finally through the length of the rows (length='r'), in the meantime it access the other spreadsheet where I want to save all those rows.

This means that the current time complexity of my algorithm is O(mpr) and it's WAY too slow.

for 15 people and 6 projects each, the amount of iterations would be 156600+ = 54,000 iterations at least (more people and more projects and more rows will be added).

is there any way to make my algorithm faster?

const members = {'Person1':'P1', 'Person2':'P2'};

const projects = {'Project1':'L','Project2':'R'}

function saveRowValue() {
  let sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  let targetSpreadsheet = SpreadsheetApp.openById('-SPREADSHEET-');
  let targetSheet = targetSpreadsheet.getSheetByName('Tracking time');

  let rowsToWrite = [];
  rowsToWrite.push(['Project', 'Initials', 'Date', 'Tracking time'])
  var rowsToSave = 1;
  for(m in members){
    Logger.log(m +' initials:'+ members[m]);
    let sourceSheet = sourceSpreadsheet.getSheetByName(m);
    for(p in projects){
      let values = sourceSheet.getRange(projects[p]+"1:"+projects[p]).getValues();
      Logger.log(values)
      let list = [null, 0,''];

      for(var i=0; i<values.length; i++){
        try{
          date = sourceSheet.getRange('B'+i).getValue();
          let val = sourceSheet.getRange(projects[p]+i)
          val = Utilities.formatDate(val.getValue(), "GMT", val.getNumberFormat())
          Logger.log(val);
          if(!(list.includes(val)) && date instanceof Date){
            //rowsToWrite.push();
            rowsToSave++;
            targetSheet.getRange(rowsToSave,1,1,4).setValues([[p, members[m], date, val]]);
          }
        }catch(e){
          Logger.log(e)
        }
      }
    }
  }
  Logger.log(rowsToWrite);

[Here you can see how much time it takes to iterate 600 rows for a single project and a single member after changing what Yuri Khristich told me to change][1] [1]: https://i.sstatic.net/CnRZY.png

Upvotes: 0

Views: 84

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14502

First step is to try to get rid of getValue() and setValue() in loops. All data should be captured at once as 2D arrays in one step and put on the sheet in one step as well. No single cell or single row operations.

Next trick depends on your workflow. Say, it's unlikely that every time all 54000+ cells need to be checked. Probably there are ranges that have no changes. You can figure out some way to indicate the changes. And process only the changed ranges. Probably, the indication could be performed with onChange() trigger. For example you can add * to the name of the sheets and columns where changes have occurred and remove these * whenever you run your script.

Reference:

Upvotes: 2

Related Questions