Delete Google Sheets row using app script

I have a Google Sheet script that when executed it change the content of the cell from "Confirmed" to "Canceled". I would like to change it in order to delete the row instead of changing the label. How to do that? Thanks

  // Check for canceled reservations
  const newCodes = newData.slice(1).map(row => row[0]);
  for (let i = 0; i < existingCodes.length; i++) {
    if (!newCodes.includes(existingCodes[i])) {
      sheet.getRange(i + 2, 2).setValue("Canceled");
      updatedRowCount++;
    }
  }

The full function here:

function appendNewReservations(sheet, newData) {
  if (newData.length <= 1) {
    return 0; // Return 0 if there's only a header row or no data
  }

  const existingData = sheet.getDataRange().getValues();
  const existingCodes = existingData.slice(1).map(row => row[0]);
  
  let updatedRowCount = 0;

  for (let i = 1; i < newData.length; i++) {
    const row = newData[i];
    const confirmationCode = row[0];

    // Convert date strings to Date objects
    row[7] = parseDate(row[7]);
    row[8] = parseDate(row[8]);

    if (!existingCodes.includes(confirmationCode)) {
      // New reservation, append it
      sheet.appendRow(row);
      updatedRowCount++;
    } else {
      // Existing reservation, update all fields
      const existingIndex = existingCodes.indexOf(confirmationCode);
      const rangeToUpdate = sheet.getRange(existingIndex + 2, 1, 1, 13); // Columns A to M
      rangeToUpdate.setValues([row.slice(0, 13)]); // Update all fields from the new data
      updatedRowCount++;
    }
  }

  // Check for canceled reservations
  const newCodes = newData.slice(1).map(row => row[0]);
  for (let i = 0; i < existingCodes.length; i++) {
    if (!newCodes.includes(existingCodes[i])) {
      sheet.getRange(i + 2, 2).setValue("Canceled");
      updatedRowCount++;
    }
  }

  // Sort the sheet by start date (oldest to newest)
  sortSheetByStartDate(sheet);

  // Set date format for columns H and I
  const dateRange = sheet.getRange(2, 8, sheet.getLastRow() - 1, 2);
  dateRange.setNumberFormat('dd / mm / yyyy');

  return updatedRowCount;
}

Deleting row if it Matches

Use Reverse for loop to delete a row. This allows safe modification of the list while iterating, ensuring that all intended elements are processed correctly. If it matches canceled, the row will be deleted.

This is based on the code given above.

Sample function:

function deleteRow(){
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const existingData = sheet.getDataRange().getValues();
  const newCodes = existingData.slice(1).map(row => row[0]);
  for (let i = existingData.length; i >= 0; i--) {
    if(newCodes[i] == "Canceled"){
      sheet.deleteRow(i+2);
    }
  }
}

You can try this modified script.

Integrated Script:

function appendNewReservations(sheet, newData) {
  if (newData.length <= 1) {
    return 0; // Return 0 if there's only a header row or no data
  }

  const existingData = sheet.getDataRange().getValues();
  const existingCodes = existingData.slice(1).map(row => row[0]);
  
  let updatedRowCount = 0;

  for (let i = 1; i < newData.length; i++) {
    const row = newData[i];
    const confirmationCode = row[0];

    // Convert date strings to Date objects
    row[7] = parseDate(row[7]);
    row[8] = parseDate(row[8]);

    if (!existingCodes.includes(confirmationCode)) {
      // New reservation, append it
      sheet.appendRow(row);
      updatedRowCount++;
    } else {
      // Existing reservation, update all fields
      const existingIndex = existingCodes.indexOf(confirmationCode);
      const rangeToUpdate = sheet.getRange(existingIndex + 2, 1, 1, 13); // Columns A to M
      rangeToUpdate.setValues([row.slice(0, 13)]); // Update all fields from the new data
      updatedRowCount++;
    }
  }

   deleteRow();

  // Sort the sheet by start date (oldest to newest)
  sortSheetByStartDate(sheet);

  // Set date format for columns H and I
  const dateRange = sheet.getRange(2, 8, sheet.getLastRow() - 1, 2);
  dateRange.setNumberFormat('dd / mm / yyyy');

  return updatedRowCount;
}

function deleteRow(){
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const existingData = sheet.getDataRange().getValues();
  const newCodes = existingData.slice(1).map(row => row[0]);
  for (let i = existingData.length; i >= 0; i--) {
    if(newCodes[i] == "Canceled"){
      sheet.deleteRow(i+2);
    }
  }
}

References:

• Reverse for loop
• Delete Row