CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstime-limiting
ysytnik
ysytnik

Reputation: 15

Google spreadsheet script time limit of execution

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();
      }
    }
  }
}

Upvotes: 1

Views: 629

Answers (2)

Pierre-Marie Richard
Pierre-Marie Richard

Reputation: 2014

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

Upvotes: 2

Max Makhrov
Max Makhrov

Reputation: 18727

I think the problem is geting data range.

Try replace this

sheet.getDataRange().clearContent();

by this:

sheet.clearContents();

Upvotes: 1

Related Questions