How to remove entire row in Google Sheets using Apps Script

Other Questions Google Sheets
1

I’m working with a Google Sheets script that currently updates a cell value from “Active” to “Inactive” when certain conditions are met. Instead of just updating the cell content, I want to completely remove the entire row from the spreadsheet. Can someone help me modify my code to achieve this?

// Check for removed bookings
const currentIds = freshData.slice(1).map(entry => entry[0]);
for (let j = 0; j < storedIds.length; j++) {
  if (!currentIds.includes(storedIds[j])) {
    worksheet.getRange(j + 2, 3).setValue("Inactive");
    modifiedRows++;
  }
}

Here’s my complete function:

function updateBookingRecords(worksheet, freshData) {
  if (freshData.length <= 1) {
    return 0;
  }

  const storedData = worksheet.getDataRange().getValues();
  const storedIds = storedData.slice(1).map(entry => entry[0]);
  
  let modifiedRows = 0;

  for (let j = 1; j < freshData.length; j++) {
    const entry = freshData[j];
    const bookingId = entry[0];

    entry[6] = formatDate(entry[6]);
    entry[7] = formatDate(entry[7]);

    if (!storedIds.includes(bookingId)) {
      worksheet.appendRow(entry);
      modifiedRows++;
    } else {
      const storedIndex = storedIds.indexOf(bookingId);
      const updateRange = worksheet.getRange(storedIndex + 2, 1, 1, 12);
      updateRange.setValues([entry.slice(0, 12)]);
      modifiedRows++;
    }
  }

  const currentIds = freshData.slice(1).map(entry => entry[0]);
  for (let j = 0; j < storedIds.length; j++) {
    if (!currentIds.includes(storedIds[j])) {
      worksheet.getRange(j + 2, 3).setValue("Inactive");
      modifiedRows++;
    }
  }

  arrangeByDate(worksheet);
  
  const dateColumns = worksheet.getRange(2, 7, worksheet.getLastRow() - 1, 2);
  dateColumns.setNumberFormat('dd / mm / yyyy');

  return modifiedRows;
}
2

To remove entire rows in your Apps Script, you should replace setValue("Inactive") with deleteRow(). However, a crucial aspect to consider is that when deleting rows in a loop, the row indices shift, causing potential issues like skipping rows or inadvertently deleting the wrong ones. Instead, it’s better to collect all the row indices you want to delete first and then perform the deletion in reverse order to prevent these index shifts. You might implement it this way: create an array to hold the indices of rows to delete, then after your checks, loop through this array backwards to delete each row using worksheet.deleteRow(). This strategy helps maintain the integrity of your script.

3

Replace worksheet.getRange(j + 2, 3).setValue("Inactive"); with worksheet.deleteRow(j + 2);. But here’s the catch - deleting rows in a forward loop screws up your indices because each deletion shifts everything up. I hit this same issue building a booking system. Here’s what fixed it: collect all the row numbers you want to delete first, then delete them backwards. During your loop, just store the row indices in an array. After the loop finishes, sort that array in descending order and delete each row. This way you’re always deleting from bottom to top, so no index shifting mess. Your counter will still work fine this way.

4

yeah, replace that setValue line with deleteRow(j + 2). Just make sure you delete backwards or the row numbers get screwed up. I grab all the row indexes first, then loop through them in reverse - never fails and won’t skip any rows.