How to delete blank rows for multiple sheets using google API

Asking Bob 0
Viewed 38

I have a case that i want to delete blank rows for multiple sheets. I want to use Google Sheet API because the SpreadsheetApp method takes too long and i get the timeout error because of it. I have 10k+ rows.

I already made my own code (Spreadsheet method version): 
function myFunction() {
 const sheet = ['', '', '', '']; //sheets name
 for (let i in sheet) {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet[i]);
  const maxrow = sheet.getMaxRows();
  const lastrow = sheet.getLastRow();
  if (maxrow - lastrow > 10) {
    ss.deleteRows(lastrow + 10, maxrow - lastrow - 10);
  }
 }
}
google-sheets-api google-api google-sheets google-apps-script
1 Answers

I believe your goal is as follows.

  • You want to modify your showing script using Sheets API.

In your script, it seems that sheet is an array. So, I think that an error occurs at const maxrow = sheet.getMaxRows();. Please be careful about this.

In this case, how about the following modification?

Modified script:

Please enable Sheets API at Advanced Google services. Ref

function myFunction() {
  const sheet = ['Sheet1', 'Sheet2',,,]; // Please set sheet names you want to use.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const requests = sheet.reduce((ar, s) => {
    const sheet = ss.getSheetByName(s);
    const maxrow = sheet.getMaxRows();
    const lastrow = sheet.getLastRow();
    if (maxrow - lastrow > 10) {
      ar.push({ deleteDimension: { range: { sheetId: sheet.getSheetId(), startIndex: lastrow + 10, endIndex: maxrow, dimension: "ROWS" } } });
    }
    return ar;
  }, []);
  if (requests.length == 0) return;
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

References:

Tanaike0
Related