error - "Method Range.getValue is heavily used by the script"

Question

I posted this question previously but did not tag it properly (and hence why I likely did not get an answer) so I thought I would give it another shot as I haven't been able to find the answer in the meantime.

The below script is giving me the message in the title. I have another function which is using the same getValue method but it is running fine. What can I change in my script to avoid this issue?

function trashOldFiles() {
 var ffile = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CtrlSht").getRange("B3:B3").getValue();
 var files = DriveApp.getFilesByName(ffile);
 while (files.hasNext()) {
  var file = files.next();
  var latestfile = DriveApp.getFileById(listLatestFile());
  if(file.getId() ==! latestfile){
  file.setTrashed(true);
   }
  }
 };

Thanks pnuts. I did edit my original post to put the correct tag on but it was a day or so later. I didn't think the question would stump everyone hence my assumption it had moved too far down the list to get any attention and my decision to re-post. Maybe there is no answer to my question, but I would be surprised based on my previous experience with this forum. — Ilya Kern, Nov 12 '14 at 06:42
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:18

score 0 · Accepted Answer · answered Nov 12 '14 at 17:40

0

Is it an error or an execution hint(the light bulb in the menu)?

are you using that method on other part of your code? probably in listLatestFile()? I got the same execution hint by calling getRange().getValue() in listLatestFile() (using a loop) and the hint always mentioned that the problem was when calling

var ffile = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CtrlSht").getRange("B3:B3").getValue();

in the function trashOldFiles() even when the actual problem was in other function.

Check if you are calling it in other place in your code, probably inside a loop.

answered Nov 12 '14 at 17:40

Gerardo

3,460
1
16
18

Thanks Gerardo. You are correct, it comes up as an execution hint and yes I am using listLatestFile(). If I run that function on it's own, it doesn't give the same error. Could it be because I am calling the listLatestFile() in my trashOldFiles() function and therefore every loop trashOldFiles() runs causes listLatestFile() to run another loop? Did you manage to overcome this somehow? – Ilya Kern Nov 13 '14 at 04:23

score 0 · Answer 2 · answered Nov 13 '14 at 05:24

OK, so Gerardo's comment about loops started to get me thinking again. I checked some other posts about how to re-use a variable and decided to put the listLatestFile() value in my spreadsheet -

  var id = result[0][1];
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CtrlSht").getRange("B5:B5").setValue(id);
  //Logger.log(id);
  return id;

and then retrieved the latest file ID from the spreadsheet to use as a comparison value for the trashOldFiles() function which worked a treat.

function trashOldFiles() {
  var tfile = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CtrlSht").getRange("B3:B3").getValue();
  var tfiles = DriveApp.getFilesByName(tfile);
  var lfile = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("CtrlSht").getRange("B5:B5").getValue();
  while (tfiles.hasNext()) {
    var tfile = tfiles.next();
     if(tfile.getId() !== lfile){
     tfile.setTrashed(true);
    }
   }
  };

Not sure if that approach was best practice but it did work for me. If anyone has suggestions for achieving this in a more elegant way, I'm all ears.

error - "Method Range.getValue is heavily used by the script"

2 Answers2