Google spreadsheet script time limit of execution

Question

I've written gs script to clear data from all cells in sheets. Spreadsheet has a lot of sheets (about 200) and I get error of time limit execution. Maybe somebody have ideas how to resolve this issue. Here example of my code.

function cleanAllOld() {

  var sheetsName = new Array();
  var destination = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/link");
  var sheets = destination.getSheets();

  for (var k = 0; k < sheets.length; k++) {

    sheetsName.push([sheets[k].getName()]);

    for (var p = 0; p < sheetsName.length; p++) {
      var sheet = destination.getSheetByName(sheetsName[p]);
      if (sheet === null) {} else {
        sheet.getDataRange().clearContent();
      }
    }
  }
}

Pierre-Marie Richard · Accepted Answer · 2017-05-11T08:41:29.557

2

I don't get all the part of your code, but I think he's too complicated for nothing. Here's is a sample which work:

function AllDelete() {

  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/link");
  var sheets = ss.getSheets();
  var i = 0;

  for(i in sheets){

    sheets[i].clearContents();

  }

}

Edit: Max is fast and is right, the getDataRange() method is using a lot of ressources

edited May 11 '17 at 08:41

answered May 11 '17 at 08:18

Pierre-Marie Richard

1,914
2
19
25

I've got error "TypeError: Cannot find function clearContent in object Sheet." without `getDataRange()` . With it script works properly. Thank you. – ysytnik May 11 '17 at 08:38
My bad, there is a 's' missing in my sample. Look at the edit. – Pierre-Marie Richard May 11 '17 at 08:41

score 1 · Answer 2 · answered May 11 '17 at 08:16

1

I think the problem is geting data range.

Try replace this

sheet.getDataRange().clearContent();

by this:

sheet.clearContents();

answered May 11 '17 at 08:16

Max Makhrov

17,309
5
55
81

Google spreadsheet script time limit of execution

2 Answers2