CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Margaret Berry
Margaret Berry

Reputation: 1

Google Sheets Archive and clear issue

     function CreateCopySheetDaily() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getSheetByName('Daily Eligibility'); 
  var range = ss.getDataRange(); 
  var ts = 'Archived_Data'+ new Date().toISOString().slice(0,10);

  ss.insertSheet(ts, ss.getSheets().length, {template: templateSheet});
  templateSheet.deleteRows(2, range.getNumRows() - 1);
}

This is the code I have been using. It works fine on my original google sheet. When I make a copy to share of my document and try to use this code... I get this error message... Exception: Those rows are out of bounds. (line 8, file "Code") The sheet will archive the information on a separate sheet but won't clear the original response sheet from the form and I get this error code. Can anyone give me some guidance?

Upvotes: 0

Views: 89

Answers (1)

Tanaike
Tanaike

Reputation: 201418

In your situation, I think that when range.getNumRows() - 1 is less than or equal to zero, the error of Exception: Those rows are out of bounds. occurs because the value of howMany of deleteRows(rowPosition, howMany) is 0 and -1. Namely, when the sheet of Daily Eligibility has only one row and no row data, such error occurs. So in order to avoid this error, how about the following modification?

Pattern 1:

In this pattern, your script is modified.

From:

templateSheet.deleteRows(2, range.getNumRows() - 1);

To:

var numRows = range.getNumRows();
if (numRows > 1) {
  templateSheet.deleteRows(2, numRows - 1);
}
  • In this modified script, when range.getNumRows() is more than 1, deleteRows is run.

Pattern 2:

In this pattern, when you don't want to modify the script, how about confirming whether the sheet of Daily Eligibility has more than 2 rows?

Reference:

Added:

For I won't have a set number of responses on the 'The Daily Eligibility' sheet. But I need to keep the 1st row and clear the rest of the content., please modify your script as follows.

From:

templateSheet.deleteRows(2, range.getNumRows() - 1);

To:

templateSheet.getRange(2, 1, templateSheet.getLastRow() - 1, templateSheet.getLastColumn()).clearContent();

Upvotes: 1

Related Questions