Method Range.getValue is heavily used by the script

Question

I have a script that works but it takes a very long time to finish. I have a number of effective copy pastes across various different workbooks and sheets. My knowledge of scripts is limited and I am quite pleased that I managed to get this to work at all. The run time is really rubbish though about 15mins and I haven't even set it up to finish all the required loops. The light bulb advice says:

Method Range.getValue is heavily used by the script. Collapse
File: student reflection copy Line: 17
The script uses a method which is considered expensive. Each invocation generates a time consuming call to a remote server. That may have critical impact on the execution time of the script, especially on large data. If performance is an issue for the script, you should consider using another method, e.g. Range.getValues().

but I don't really know what I can do. I cannot start this project again and there will always be copy pasting across different sheets to set it all up. Any help would be greatly received. This is my working code:

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var studentID = spreadsheet.getSheetByName('Sheet11');
  var sourceSheet = spreadsheet.getSheetByName('Sheet6');
  var createLink = spreadsheet.getSheetByName('Sheet14');

  var i ,j, k;

  for( var k = 1; k< 3; k++) {

    // copy studentID
    studentID.getRange(k, 2).copyTo(createLink.getRange(4, 4 ,{contentsOnly: true}));

    for( var i = 1; i< 21; i++) {
      //copy students subject
      var subjectName = sourceSheet.getRange(i+1,7*k-5).getValue();
      if (subjectName =="") {
        break;
      }else {
        createLink.getRange(4, 6).setValue(subjectName)
        //copy row number of reflection
        for (var j = 1; j<31; j++){
          var rownumber = j+5;
          createLink.getRange(4, 8).setValue(rownumber);
          SpreadsheetApp.flush(); 
          //copy link of student reflection into teacher marksheet
          var markSheetID = sourceSheet.getRange(i+1, 7*k-3).getValue();
          var groupNumber = sourceSheet.getRange(i+1,7*k-2).getValue();
          var MarkSheet = SpreadsheetApp.openById(markSheetID).getSheets()[groupNumber-1];
          var linkvalue = createLink.getRange(4, 10).getValue();
          MarkSheet.getRange(2+k, 6*j).setValue(linkvalue);}
      }
    }
  }
}

I know it isn't great but I have never done this stuff before. Any advice on how I can speed up this process?

Update: Please find attached the workbook. There is student information that is pasted into sheet 14 to create an import range link that is then pasted back into the teacher mark book. This process is repeated for each block of subjects and then for every student. I do not know how to use arrays and how to code using these stores of information rather than every individual value. copy of workbook

I am not sure why you would say that? I timed it. Perhaps as I was doing other things on my laptop whilst running the script the time was a little longer but the original point remains that the script takes a long time to complete. — ck88, Oct 12 '18 at 17:54
@I'-'I G Suite accounts have a execution limit of 30 minutes. — Rubén, Oct 12 '18 at 20:18
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:19

score 2 · Answer 1 · answered Oct 12 '18 at 20:26

It's very likely that the system message was caused by using setValue inside nested for-loops (there are three for-loops) plus having SpreasheetApp.flush() inside the nested for-loops what prevents the Apps Script engine to use it's built-in write optimization algorithms.

Try to avoid using setValue() inside for-loops. For this you could use a JavaScript array to hold the values then use setValues() to pass several values at once instead of passing one by one.

score 0 · Answer 2 · answered Oct 13 '18 at 00:49

It's kind of hard to do this in a vacuum without the ability to run the program with real data.

There's not that much to change. But if you can live with the SpreadsheetApp.flush that would be good. Other than that I could only replace a couple getValue() s with one getValues(). I just commented out the lines I replaced.

  function myFunction() {
  var spreadsheet = SpreadsheetApp.getActive();
  var studentID = spreadsheet.getSheetByName('Sheet11');
  var sourceSheet = spreadsheet.getSheetByName('Sheet6');
  var createLink = spreadsheet.getSheetByName('Sheet14');
  for( var k = 1; k< 3; k++) {
    // copy studentID
    studentID.getRange(k, 2).copyTo(createLink.getRange(4, 4 ,{contentsOnly: true}));
    for( var i = 1; i< 21; i++) {
      //copy students subject
      var subjectName = sourceSheet.getRange(i+1,7*k-5).getValue();
      if (subjectName =="") {
        break;
      }else {
        createLink.getRange(4, 6).setValue(subjectName)
        //copy row number of reflection
        for (var j = 1; j<31; j++){
          var rownumber = j+5;
          createLink.getRange(4, 8).setValue(rownumber);
          //SpreadsheetApp.flush(); 
          //copy link of student reflection into teacher marksheet
          var mgvA=sourceSheet.getRange(i+1, 7*k-3, 1, 2).getValues();
          //var markSheetID = sourceSheet.getRange(i+1, 7*k-3).getValue();
          //var groupNumber = sourceSheet.getRange(i+1,7*k-2).getValue();
          var MarkSheet=SpreadsheetApp.openById(mgvA[0][0]).getSheets()[mgvA[0][1]-1];
          //var MarkSheet = SpreadsheetApp.openById(markSheetID).getSheets()[groupNumber-1];
          var linkvalue = createLink.getRange(4, 10).getValue();
          MarkSheet.getRange(2+k, 6*j).setValue(linkvalue);}
      }
    }
  }
}

Method Range.getValue is heavily used by the script

2 Answers2