Method is heavily used by the script

Question

I am very new on using Google Apps Script and has a shallow knowledge on programming. What I am trying to do is copy the values of specific columns to a different Spreadsheet. Here's my code:

function myFunction() {

  var ss = SpreadsheetApp.getActive();
  var responses = ss.getSheetByName("Responses ID");//Where ID's of the spreadsheets are listed.
  var consolidatedSheet = ss.getSheetByName("Consolidated");//Where the data should be pasted.

  var responseColValues = responses.getRange(2,2, responses.getMaxRows() -1).getValues();
  var responsesIds = [i for each (i in responseColValues)if (isNaN(i))];
  var ssSelected = SpreadsheetApp.openById(responsesIds[0]);
  var selectedSheets = ssSelected.getSheets();

  for (i=0; i<3; i++){

  var maxRows = selectedSheets[i].getLastRow()-1;
  var x=2, y=2;
  var lastRow = consolidatedSheet.getLastRow()+1;

    for (j=0; j<maxRows; j++){

      var eventID = selectedSheets[i].getRange(y,2).getValue();
      var employeeName = selectedSheets[i].getRange(y,3).getValue();
      var productionDate = selectedSheets[i].getRange(y,4).getValue();

      var consolidatedSheetCell = consolidatedSheet.getRange(lastRow,1).setValue(eventID);
      var consolidatedSheetCell = consolidatedSheet.getRange(lastRow,2).setValue(employeeName);
      var consolidatedSheetCell = consolidatedSheet.getRange(lastRow,3).setValue(productionDate);

      y++;
      lastRow++;

    }

  }

}

However, I am experiencing this notification on the Execution hints (light bulb icon): screenshot of the message. I am thinking that my code can be simplified. I am just not sure how to do it. Thank you in advance.

Possible duplicate of [Long processing time likely due to getValue and cell inserts](https://stackoverflow.com/questions/35289183/long-processing-time-likely-due-to-getvalue-and-cell-inserts) — Rubén, Sep 05 '19 at 18:17

score 1 · Answer 1 · answered Aug 16 '17 at 18:17

1

Every line with .getRange().getValue() and getRange.setValue() is a call to the file. Since you have these inside a for(){} loop, they are being called many times. Your goal is to limit these to as few as possible. Since you can read and write a range, you could do something similar to this:

function myFunction() {

  var ss = SpreadsheetApp.getActive();
  var responses = ss.getSheetByName("Responses ID");//Where ID's of the spreadsheets are listed.
  var consolidatedSheet = ss.getSheetByName("Consolidated");//Where the data should be pasted.

  var responseColValues = responses.getRange(2,2, responses.getMaxRows() -1).getValues();
  var responsesIds = [i for each (i in responseColValues)if (isNaN(i))];
  var ssSelected = SpreadsheetApp.openById(responsesIds[0]);
  var selectedSheets = ssSelected.getSheets();

  for (i=0; i<3; i++){

  var maxRows = selectedSheets[i].getLastRow()-1;
  var y=2;
  var lastRow = consolidatedSheet.getLastRow()+1;

    var copyValues = selectedSheets[i].getRange(y,2, maxRows, 4).getValues();
    consolidatedSheet.getRange(lastRow,1, maxRows, 4).setValues(copyValues);


  }

}

answered Aug 16 '17 at 18:17

Karl_S

3,364
2
19
33

Thank you for your quick answer. That really helped me understand it. However, I am planning to get data from columns that are apart from each other. For example, columns A,B,C,M,P,R. – SilverFangPH Aug 16 '17 at 19:04
Collecting from different columns is fine. Are you setting them to a column range i the new location? A, B C, M, P, and R go to columns D though I? The order can be any such as A, R, C, B, M, P. The goal is are you writing to any connected columns? – Karl_S Aug 16 '17 at 19:29
Yes. I would like to put the values of A, B, C, M, P, R to columns A to F in other worksheet. Sorry for the late reply. They had me off this project for a moment. – SilverFangPH Sep 06 '17 at 15:15
Take a look a [this reply to the question of writing to a range](https://webapps.stackexchange.com/a/106504/81031) and see if you can apply the information there. – Karl_S Sep 06 '17 at 15:31

Method is heavily used by the script

1 Answers1