Clear script error on next day form entries

user_14163915 0
Viewed 52

I am using the following script to clear Google form entries at midnight each day.

function clearRange() {
    var sheetActive = SpreadsheetApp.openById("1DNeHRC41ryRY9s8wZmlqmoIEhm8MqD601BvqoV26V3o").getSheetByName("Form responses 1");
    sheetActive.getRange('A2:E').clearContent();
}

Problem:

  • The following day, the form entries are added to the sheet starting at the row below the previous day's entries, not row 2.

Example:

  • Yesterday, 10 form entries were entered on the sheet (rows 2 to 11). The script runs at midnight and clears the entries.
  • Today, five entries were entered on the form but were entered from row 12 onwards.

I need the sheet to clear and reset, to start entering form entries in row 2 every day.

Is this possible? If so, what changes are needed to the formula? Thank you for your assistance.

google-forms google-apps-script google-sheets
1 Answers

Solution:

Try to delete the rows instead:

function clearRange() {
  const sheetActive = SpreadsheetApp.openById("1DNeHRC41ryRY9s8wZmlqmoIEhm8MqD601BvqoV26V3o").getSheetByName("Form responses 1");
  const form_size = sheetActive.getLastRow()-1;
  sheetActive.deleteRows(2, form_size);
}

Before you start using this solution, only for the first time, delete manually all the rows; starting from the second row until the row before the new entry will be added. So the new entry will be at the second row.

Recommendation:

However, it is not recommended to touch the Form responses sheet. I would advice you to create a separate sheet to query only the records of today and let the raw data be in the Form responses sheet. In this way, you don't need to clear or delete the old form entries, but you also don't touch the raw data which might be used as a reference in the future.

References:

user_112252910
Related