Skip to content
Advertisement

Deleting Data In Bulk – Google sheets

I found this piece of code and need to modify it so that if it doesn’t find a result it will return and keep running next lines of code

function DeleteTEXT_BULK() {
 // will delete in bulk whatever the text finder finds. tested and working
 // YOU MUST ENABLE THE SHEETS API - RESOURCES / ADVANCED GOOGLE SERVICES / GOOGLE SHEETS API
 // cannot delete blank rows

const sheetName = "Sheet 1"; // Please set the sheet name.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName);
const sheetId = sheet.getSheetId();
const requests = sheet
.getRange(`A1:E${sheet.getLastRow()}`)
.createTextFinder("^JOHN")
.matchCase(true)
.useRegularExpression(true)
.findAll()
.map(r => r.getRow())
.reverse()
.map(r => ({delete Dimension:{range:{sheetId:sheetId,startIndex:r - 
1,endIndex:r,dimension:"ROWS"}}}));
Sheets.Spreadsheets.batchUpdate({requests: requests}, ss.getId());
}

So if it doesn’t find “JOHN” it currently stops running and throws an error.

I would like it to move onto next piece of code if no result found.

appreciate any help, this is my first post

Advertisement

Answer

I believe your goal is as follows.

  • You want to continue to run the script even when the value of JOHN is not found.

In this case, how about the following modification?

Modified script:

function DeleteTEXT_BULK() {
  const sheetName = "Sheet 1"; // Please set the sheet name.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const sheetId = sheet.getSheetId();
  const ranges = sheet
    .getRange(`A1:E${sheet.getLastRow()}`)
    .createTextFinder("^JOHN")
    .matchCase(true)
    .useRegularExpression(true)
    .findAll();
  if (ranges.length > 0) {
    const requests = ranges.map(r => r.getRow())
      .reverse()
      .map(r => ({ deleteDimension: { range: { sheetId: sheetId, startIndex: r - 1, endIndex: r, dimension: "ROWS" } } }));
    Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
  }

  // do something.

}
  • In this modification, when the value of JOHN is not found, by if (ranges.length > 0) {}, the script of Sheets.Spreadsheets.batchUpdate is skipped. By this, you can continue to run the script.
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement