How to Delete older rows on Form input data

Question

I have 2 sheets in a Google workbook. A form inputs data to Sheet2, and a cell on Sheet1 shows the value of the cell in col B in the last row of Sheet2.

Sheet2:

1 Timestamp | UUID:
2 SomeDate1 | 0d6d7761-8540-1c5a-072b-f32868eb7f9b
3 SomeDate2 | d6b0df12-8dc9-eeff-5eef-ad064868fb31
4 SomeDate3 | a64ac46a-bb53-f540-f526-01cb48dac8f2
5 SomeDate4 | 826b3c6d-45cc-1b11-f2cd-e2a91eba091b
6 SomeDate5 | 00965bc4-77a1-aa87-52cd-ba11f472aa32

Sheet 1:

1 UUID:    | =INDEX(FILTER(Sheet2!B1:B,NOT(ISBLANK(Sheet2!B1:B))),COUNTA(Sheet2!B1:B))
              (which shows correctly as 00965bc4-77a1-aa87-52cd-ba11f472aa32)

When a form submission adds a new row to Sheet2, I need to delete older rows automatically, in some way.

After reading several pages of similar questions on StackExchange, I've learned that scripts can not delete Form input data, but a work-around is to use a time-driven Trigger. (How to delete row when a cell is modified?)

So, I found this script and added it, naming it DeleteOldRows.gs:

    function acraCleanup() {
    var rowsToKeep = 2; //NUMBER OF ROWS TO KEEP
    var rows = SpreadsheetApp.getActiveSheet().getLastRow();
    var numToDelete = rows - rowsToKeep -1;
    SpreadsheetApp.getActiveSheet().deleteRows(2, numToDelete);
    }

I then set a Timer trigger, to run that script.

The timer execution log shows the error "Exception: Those rows are out of bounds. at acraCleanup(DeleteOldRows:5:33)" which would be "deleteRows(2, numToDelete)"

Is the script incorrect, or is the error due to the fact the data is input via a Form?

Also, I have other sheets in the workbook with other data, and I need to make sure that the script only deletes the older rows on Sheet2, but I'm not sure how to specify that. I tried replacing "getActiveSheet()" with "getSheetByName('Sheet2')" and the error said "getSheetByName is not a function". Same with "getSheet('Sheet2')".

Cooper · Accepted Answer · 2021-07-29T23:50:31.537

0

function deleteAllOldRows() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Form Responses 9');
  const lr = sh.getLastRow();
  if(lr - 2)sh.deleteRows(2,lr-2);
}

edited Jul 29 '21 at 23:50

answered Jul 29 '21 at 23:41

Cooper

59,616
6
23
54

pgSystemTester · Answer 2 · 2021-07-29T23:47:32.540

I would not handle this the way you've set it up. Rather than have a script running all the time, I'd scope your Form to insert the values you received on a submission to just continually overwrite the same line.

However, if you insist on doing the way you scoped it, this will continually delete any rows between 1 and 3 or more, while leaving the last row in place.

/**
 * @OnlyCurrentDoc
 */
function acraCleanup() {
  const rowsToKeep = 1; //NUMBER OF ROWS TO KEEP
  const nameOfSheet = "Sheet2";

  const theWS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nameOfSheet);
  var rows = theWS.getLastRow();
  var numToDelete = rows - rowsToKeep - 1;
  theWS.deleteRows(2, numToDelete);
}

Also, the reason you're getting an error on the getActiveSheet() is because you are not specifying the spreadsheet FILE. Truthfully, Google should get rid of this as the language is a bit confusing and it doesn't add much ease of use. TO properly identify an sheet, you need: SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2")

How to Delete older rows on Form input data

2 Answers2