CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
cheslijones
cheslijones

Reputation: 9194

Non-iterative solution to delete visible rows with Apps Script?

I have a file that needs to get copied about 125 times: one copy for each user. There are about 1,000 rows in the sheet. I elected to just copy this file so that protected ranges, formatting, filters, etc. follow it without having to recreate them.

The Apps Script I have thus far:

  1. Gets an array of unique emails in a specified column (about 125 unique emails).
  2. Iterates through that array and while doing so, makes a copy of the "Master" file (the one with 1,000 rows).
  3. Adds a filter to the columns in the duplicate file and excludes the current unique email address.
  4. Then iterates through each row and checks for !documentObject.isRowHiddenByFilter(row) and deletes the row if true.
  5. Finally, removes the filter which shows the intended user only the records that are relevant to them.

For removing the visible rows, I'm doing the following:

  deleteVisibleRows(documentObject, lastRow) {
    for (let r = lastRow; r > 1; r--) {
      if(!documentObject.isRowHiddenByFilter(r)) {
        documentObject.deleteRow(r);
      }
    }
  }

This is really slow: 125 iterations * 1000 rows = 125,000 rows.

I'd much prefer to be able to just selectRange and deleteVisibleRows without iteration to speed this up.

From what I can tell, there isn't a method for just deleting visible rows. It is very likely visible and not visible rows will not be consecutive either.

Any suggestions?

Upvotes: 0

Views: 153

Answers (1)

Tanaike
Tanaike

Reputation: 201398

I believe your goal is as follows.

  • You want to reduce the process cost of your script.

In this case, I thought that when Sheets API is used, the process cost can be reduced. So in this answer, I would like to propose using Sheets API.

From your script, I understood that your values of documentObject and lastRow are the object of the Class Sheet and the integer number, respectively. In this answer, these values are used in the request for Sheets API. When your script of deleteVisibleRows is modified, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

function deleteVisibleRows(documentObject, lastRow) {
  const spreadsheetId = documentObject.getParent().getId();
  const sheetName = documentObject.getSheetName();
  const sheetId = documentObject.getSheetId();
  const rowMetadata = Sheets.Spreadsheets.get(spreadsheetId, { ranges: [sheetName], fields: "sheets(data(rowMetadata(hiddenByFilter)))" }).sheets[0].data[0].rowMetadata;
  const requests = rowMetadata.reduceRight((ar, { hiddenByFilter }, i) => {
    if (!hiddenByFilter && i > 0 && i + 1 < lastRow) ar.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i + 1, dimension: "ROWS" } } });
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({requests}, spreadsheetId);
}

Note:

  • When this function is run, the showing rows are deleted. Please be careful about this. So when you test this script, I would like to recommend using a sample Spreadsheet.

References:

Upvotes: 1

Related Questions