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, byif (ranges.length > 0) {}
, the script ofSheets.Spreadsheets.batchUpdate
is skipped. By this, you can continue to run the script.