Sheets

/**
 * Activate the specified sheet, then duplicates it, and activate the duplicate.
 */
function activateAndDuplicateSheet() {
  const sheetToActivate = "TEMPLATE" // Configure the name of the sheet to activate.
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  ss.getSheetByName(sheetToActivate).activate(); // getSheetByName and set it as active.
  Logger.log(SpreadsheetApp.getActiveSheet().getSheetName())
  ss.duplicateActiveSheet(); // Duplicate the active sheet. This process automatically activates the duplicate sheet.
  Logger.log(SpreadsheetApp.getActiveSheet().getSheetName())
}

/**
 * Remove ALL data validations from the activeSheet.
 * Note: All values will be left as they are.
 */
function removeAllDataValidations() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const sh = ss.getActiveSheet();
  sh.getDataRange().clearDataValidations();
}

/**
 * Clears all static (non-formula) values and formulas from the specified range.
 * This method removes everything within the range, leaving the cells completely blank.
 * Use this when you want to fully reset the contents of a range, not just clear values.
 */
function clearData() {
  const ss = "target_file_id"; // Configure the target Sheets file id.
  const sh = "sheet_name"; // Configure the sheet name in the target Sheets file.
  const range = "A2:C10"; // Configure the range in the target sheet.
  const targetFileRange = SpreadsheetApp.openById(ss).getSheetByName(sh).getRange(range);
  targetFileRange.clearContent();
}
/**
 * Clears all static (non-formula) values from the specified range while preserving formulas.
 * This is achieved by retrieving the existing formulas and immediately reapplying them
 * using setFormulas(), which effectively removes any manually entered or imported values
 * while leaving the formula structure intact.
 */
function clearDataNotFormulas() {
  const ss = "target_file_id"; // Configure the target Sheets file id.
  const sh = "sheet_name"; // Configure the sheet name in the target Sheets file.
  const range = "A2:C10"; // Configure the range in the target sheet.
  const targetFileRange = SpreadsheetApp.openById(ss).getSheetByName(sh).getRange(range);
  const formulas = targetFileRange.getFormulas();
  targetFileRange.setFormulas(formulas);
}

/**
 * Convert formulas in the active range to 'Values only'.
 * Author: Mr Shane
 * Version: 2024-01-26
 */
function convertToValues(){
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const sheet = ss.getActiveSheet(); // Get the active sheet.
  sheet.getActiveRange().setValues(sheet.getActiveRange().getValues()); // Get all values in the active range and set them to the active range.
}

/**
 * Convert all formulas on EVERY tab in a Sheets file to 'Values only'.
 * Note: This doesn't impact Custom formulas in Conditional Formatting.
 */
function convertAllFormulasToValuesOnly() {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(); // Get all sheets in the active Spreadsheet file.
  sheets.forEach(sh => { // For each sheet in the file...
    const range = sh.getDataRange(); // Get the data range...
    range.copyTo(range, { contentsOnly: true }); // Copy the contents of the range back to the range.
  });
}

/**
 * Append values from a vertical range as a new row on another sheet, then clear the range of values.
 * Goal: To use Sheets as a form submission tool.
 * Author: Mr Shane.
 * Version 2024-03-29
 */
function appendToDatabase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName("Form"); // Configure the source sheet name.
  const sourceRange = sourceSheet.getRange("B1:B6"); // Configure the source sheet range.
  const targetSheet = ss.getSheetByName("database"); // Configure the target sheet name.
  const values = sourceRange.getValues(); // Get the values from the source range.
  targetSheet.appendRow(values.flat()); // Append the values as a new row on the target sheet.
  sourceRange.clearContent(); // Clear the values from the source range.
}

/**
 * Count ALL cells in the Sheets file.
 */
function countCells() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const sheets = ss.getSheets(); // Get all sheets in the file.
  let totalCells = 0 // Initialise cell count.
  for( i =0; i < sheets.length; i++){ // Loop through each sheet in the file.
    totalCells = totalCells + sheets[i].getMaxRows() * sheets[i].getMaxColumns(); // Add the total cells of each sheet to the totalCells count.
  }
  Browser.msgBox("Total Cells in this file =  " + totalCells); // Display the results in a popup.
  Logger.log("Total Cells in this file =  " + totalCells); // Log the results.
}

/**
 * Count ALL comments in a Sheets file.
 * Prerequisites: Drive API v3
 */
function countComments() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const commentsList = Drive.Comments.list(ss.getId(),{ fields: "*" }); // Updated for Drive API v3.
  const totalComments = commentsList.comments.length; // Get the number of comments.
  ss.toast('Number of comments: ' + totalComments, 'Comment Count', 5); // Show toast message with the number of comments.
}

/**
 * Count the number of cells in a range that have a specific background color.
 * Example usage: =countColoredCells("A1:H2","D3")
 * Note: you can use a checkbox to trigger recalculation, like this: =countColoredCells("A1:H2","D3",checkboxCellRef)
 * @param {string} countRange A string representing the range to be counted (e.g., "A1:H2").
 * @param {string} colorRef A string representing the cell with the desired background color (e.g., "E3").
 * @return {number} The number of cells with the specified color.
 * @customfunction
 *
 * Author: Mr Shane
 * Version: 2025-08-28
 */
function countColoredCells(countRange, colorRef) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet.
  const backgrounds = sheet.getRange(countRange).getBackgrounds(); // Get the background colors of the range to analyse.
  const color = sheet.getRange(colorRef).getBackground(); // Get the background color of the reference cell.
  const flatBackgrounds = backgrounds.flat(); // Flatten the 2D array of backgrounds into a single 1D array.
  const count = flatBackgrounds.reduce((acc, currentBg) => { // Use reduce() to iterate over the array and count the cells.
    if (currentBg === color) {
      return acc + 1;
    }
    return acc;
  }, 0); // The initial value for the count is 0.
  return count;
}

/**
 * Counts sheets in a file whose names begin with a specified keyword/prefix. (Case Sensitive)
 * Author: Mr Shane
 * Version: 2025-05-08
 */
function countNamedSheets() {
  const keywordCell = "A2"; // Configure the source (keyword) cell.
  const outputCell = "B2"; // Configure the target (count) cell
  const fileId = "1X_h-fGwcEglXjrU5ZzJOYbPTUa5mw1bQGPvabk9nnz4"; // Configure the source file ID.
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the currently active sheet.
  const keyword = activeSheet.getRange(keywordCell).getValue().toString().trim(); // Read and trim the keyword.
  if (!keyword) { // IF the keyword is empty or not provided.
    activeSheet.getRange(outputCell).setValue("No keyword available"); // Write a message.
    return; // Exit the function early
  }
  const externalSpreadsheet = SpreadsheetApp.openById(fileId); // Open the external spreadsheet using its file ID.
  const count = externalSpreadsheet.getSheets().filter(sheet => { // Filter sheets based on name match.
    const name = sheet.getName(); // Get the sheet name.
    return name.startsWith(keyword); // Match prefix.
  }).length; // Count how many sheets matched.
  activeSheet.getRange(outputCell).setValue(count); // Write the count result.
}

/**
 * Counts sheets in a file whose names begin with a specified keyword/prefix. (Case Insensitive)
 * Author: Mr Shane
 * Version: 2025-05-08
 */
function countNamedSheets() {
  const keywordCell = "A2"; // Configure the source (keyword) cell.
  const outputCell = "B2"; // Configure the target (count) cell
  const fileId = "1X_h-fGwcEglXjrU5ZzJOYbPTUa5mw1bQGPvabk9nnz4"; // Configure the source file ID.
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the currently active sheet.
  const keyword = activeSheet.getRange(keywordCell).getValue().toString().trim().toLowerCase(); // Read and trim the keyword as lowercase.
  if (!keyword) { // IF the keyword is empty or not provided.
    activeSheet.getRange(outputCell).setValue("No keyword available"); // Write a message.
    return; // Exit the function early
  }
  const externalSpreadsheet = SpreadsheetApp.openById(fileId); // Open the external spreadsheet using its file ID.
  const count = externalSpreadsheet.getSheets().filter(sheet => { // Filter sheets based on name match.
    const name = sheet.getName().toLowerCase(); // Get the sheet name as lowercase.
    return name.startsWith(keyword); // Match prefix.
  }).length; // Count how many sheets matched.
  activeSheet.getRange(outputCell).setValue(count); // Write the count result.
}

/**
 * Delete ALL responses from ALL Forms linked to the Sheets file
 */
function deleteAllFormResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const sheets = ss.getSheets(); // Get all sheets in the file.
  sheets.forEach(sheet => { // For each sheet in the file...
    const formUrl = sheet.getFormUrl(); // Get the Form URL linked to the sheet.
    if (formUrl) { // IF a form is linked to the sheet...
      const form = FormApp.openByUrl(formUrl); // Open the form.
      form.deleteAllResponses(); // Delete all responses from the form.
     Logger.log(`Deleted all responses from form linked to sheet: ${sheet.getName()}`); // Log the sheet that was processed.
     }
  });
  Logger.log('All linked form responses have been deleted.'); // Log when the script is complete.
}

/**
 * Delete corresponding responses from Form (based on Timestamp only) after rows deleted from sheet.
 * Manually run this script to delete corresponding Form responses based on their timestamps after deleting random rows from the sheet.
 */
function deleteFormResponsesBasedOnTimestamps() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const sheet = ss.getActiveSheet(); // Get the active sheet.
  const dataValues = sheet.getSheetValues(2, 1, sheet.getLastRow() - 1, 1).flat(); // Get the timestamps.
  const formUrl = sheet.getFormUrl(); // Get the form URL.
  const form = FormApp.openByUrl(formUrl); // Open the form by its URL.
  const responses = form.getResponses(); // Get the form responses.
  const sheetTimestamps = new Set(dataValues.filter(Boolean).map(date => new Date(date).getTime())); // Create a set of timestamps currently in the sheet.
  responses.forEach(response => { // For each response...
    const responseTimestamp = response.getTimestamp().getTime(); // Get the response timestamp.
    if (!sheetTimestamps.has(responseTimestamp)) { // IF the response timestamp doesn't exist...
      form.deleteResponse(response.getId()); // Delete the response from the Form.
    }
  });
}
/**
 * Delete corresponding responses from Form (based on Timestamp & Logged Email) after rows deleted from sheet.
 * Manually run this script to delete corresponding Form responses based on their timestamps and email addresses after deleting random rows from the sheet.
 * Prerequisites:
 * 1. Assumes the form collects email addresses using Settings > Responses > Collect email addresses > 'Verified' or 'Responder Input'.
 * 2. The email collection setting must be done before creating questions so that the email column is in column 2 of the linked sheet.
 */
function deleteFormResponsesBasedOnTimestampsAndEmails() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file.
  const sheet = ss.getActiveSheet(); // Get the active sheet.
  const dataValues = sheet.getSheetValues(2, 1, sheet.getLastRow() - 1, 2); // Get the timestamps and email addresses.
  const formUrl = sheet.getFormUrl(); // Get the form URL.
  const form = FormApp.openByUrl(formUrl); // Open the form by its URL.
  const responses = form.getResponses(); // Get the form responses.
  const sheetEntries = new Set(dataValues.filter(row => row[0] && row[1]).map(row => `${new Date(row[0]).getTime()}_${row[1]}`)); // Create a set of {timestamp, email} pairs currently in the sheet.
  responses.forEach(response => { // For each response...
    const responseTimestamp = response.getTimestamp().getTime(); // Get the response timestamp.
    const responseEmail = response.getRespondentEmail(); // Get the email address.
    if (!sheetEntries.has(`${responseTimestamp}_${responseEmail}`)) { // IF the response {timestamp, email address} pair doesn't exist...
      form.deleteResponse(response.getId()); // Delete the response from the Form.
    }
  });
}

/**
 * Delete empty columns from the activeSheet
 * Author: Mr Shane
 * Version: 2024-08-27
 */
function deleteEmptyColumns() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the activeSheet.
  const range = sheet.getDataRange(); // Get the dataRange of the activeSheet.
  const numColumns = range.getNumColumns(); // Get the number of columns.
  const numRows = range.getNumRows(); // Ge the number of rows.
  for (let col = numColumns; col >= 1; col--) {
    const columnData = sheet.getRange(2, col, numRows - 1).getValues(); // Get all values in the column after the header.
    const isEmpty = columnData.every(cell => cell[0] === '');
    if (isEmpty) {
      sheet.deleteColumn(col); // Delete the column if all cells below the header are empty.
    }
  }
}

/**
 * Email notification of items dated in the near furture
 * This script is useful for cases where you need data from columns
 * that have been identified by header text in row 1
 * rather than by column letters or numbers.
 */
/** USER CONFIGURED VARIABLES */
const columnKey1 = "Description"; // Configure the header label for the first data column (e.g., Description).
const columnKey2 = "Quantity"; // Configure the header label for the second data column (e.g., Amount).
const dateKey = "Expiry"; // Configure the header label for the date column to filter by (e.g., Expiry).
const emailRecipient = "your_email_address"; // Configure the recipient email address for the notification.
/**
 * Email notification for items dated the following day.
 * Author: Mr Shane
 * Version: 2025-05-03
 */
function sendEmailExpiringTomorrow() {
  const today = new Date(); // Get today's date.
  const tomorrow = new Date(today); tomorrow.setDate(today.getDate() + 1); // Calculate tomorrow's date.
  tomorrow.setHours(0, 0, 0, 0); // Zero out the time portions for exact date comparison.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet.
  const [header, ...rows] = sheet.getDataRange().getValues(); // Get all data and split header from rows.
  const key1Index = header.indexOf(columnKey1); // Find index of the columnKey1 column.
  const key2Index = header.indexOf(columnKey2); // Find index of the columnKey2 column.
  const dateIndex = header.indexOf(dateKey); // Find index of the dateKey column.
  const tomorrowItems = rows // Start processing rows to find items expiring tomorrow.
    .filter(row => {
      const date = new Date(row[dateIndex]); // Get the date from the row.
      date.setHours(0, 0, 0, 0); // Normalize time for accurate comparison.
      return date.getTime() === tomorrow.getTime(); // Keep if date matches tomorrow exactly.
    })
    .map(row => {
      const date = new Date(row[dateIndex]).toLocaleDateString('sv-SE'); // Use the ISO 8601 standard to format the date.
      return `${columnKey1}: ${row[key1Index]}, ${columnKey2}: ${row[key2Index]}, ${dateKey}: ${date}`; // Build the output string.
    });
  if (tomorrowItems.length) { // If there are any items expiring tomorrow.
    MailApp.sendEmail({ // Send an email with the list of expiring items.
      to: emailRecipient, // Set the email recipient.
      subject: "Items expiring tomorrow", // Set the email subject.
      body: `The following items will expire tomorrow:\n\n${tomorrowItems.join('\n')}` // Set the email body.
    });
    Logger.log("Tomorrow's expiration notification sent."); // Log the email was sent.
  } else {
    Logger.log("No items are expiring tomorrow."); // Log when there’s nothing to report.
  }
}
/**
 * Email notification for items dated within the next 30 days.
 * Author: Mr Shane
 * Version: 2025-05-03
 */
function sendEmail30DayExpirations() {
  const today = new Date(); // Get today's date.
  const limitDate = new Date(today); limitDate.setDate(today.getDate() + 30); // Set the limit date to 30 days from today.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet.
  const [header, ...rows] = sheet.getDataRange().getValues(); // Get all data and split header from rows.
  const key1Index = header.indexOf(columnKey1); // Find index of the columnKey1 column.
  const key2Index = header.indexOf(columnKey2); // Find index of the columnKey2 column.
  const dateIndex = header.indexOf(dateKey); // Find index of the dateKey column.
  const expiringItems = rows // Start processing rows to find expiring items.
    .filter(row => { // Filter rows with dates within the next 30 days.
      const date = new Date(row[dateIndex]); // Get the date from the row.
      return date >= today && date <= limitDate; // Keep if date is within the next 30 days.
    })
    .map(row => { // Map each filtered row to a formatted string.
      const date = new Date(row[dateIndex]).toLocaleDateString('sv-SE'); // Use the ISO 8601 standard to format the date.
      return `${columnKey1}: ${row[key1Index]}, ${columnKey2}: ${row[key2Index]}, ${dateKey}: ${date}`; // Build the output string.
    });
  if (expiringItems.length) { // If there are any expiring items.
    MailApp.sendEmail({ // Send an email with the expiring item list.
      to: emailRecipient, // Set the email recipient.
      subject: "Items expiring in the next 30 days", // Set the email subject.
      body: `The following items have upcoming expiration dates:\n\n${expiringItems.join('\n')}` // Set the email body.
    });
    Logger.log("Email sent successfully."); // Log success.
  } else {
    Logger.log("No items are expiring in the next 30 days."); // Log when there's nothing to report.
  }
}
/**
 * Email notification on last day of the month for items dated next month.
 * Author: Mr Shane
 * Version: 2025-05-03
 * Prerequisites: 'Time-driven' trigger with 'Day timer'
 */
function sendEmailNextMonthExpirations() {
  const today = new Date(); // Get today's date.
  const currentMonth = today.getMonth(); // Get the current month (0–11).
  const currentYear = today.getFullYear(); // Get the current year.
  const lastDayOfMonth = new Date(currentYear, currentMonth + 1, 0); // Get the last day of the current month.
  if (today.getDate() !== lastDayOfMonth.getDate()) return; // Exit if today is not the last day of the month.
  const nextMonthStart = new Date(currentYear, currentMonth + 1, 1); // First day of the next month.
  const nextMonthEnd = new Date(currentYear, currentMonth + 2, 0); // Last day of the next month.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet.
  const [header, ...rows] = sheet.getDataRange().getValues(); // Get all data and split header from rows.
  const key1Index = header.indexOf(columnKey1); // Find index of the columnKey1 column.
  const key2Index = header.indexOf(columnKey2); // Find index of the columnKey2 column.
  const dateIndex = header.indexOf(dateKey); // Find index of the dateKey column.
  const nextMonthItems = rows // Start processing rows to find next-month expirations.
    .filter(row => { // Filter rows based on date falling within next month.
      const date = new Date(row[dateIndex]); // Get the date from the row.
      return date >= nextMonthStart && date <= nextMonthEnd; // Keep if date is within next month.
    })
    .map(row => { // Map each filtered row to a formatted string.
      const date = new Date(row[dateIndex]).toLocaleDateString('sv-SE'); // Use the ISO 8601 standard to format the date.
      return `${columnKey1}: ${row[key1Index]}, ${columnKey2}: ${row[key2Index]}, ${dateKey}: ${date}`; // Build the output string.
    });
  if (nextMonthItems.length) { // If there are any items expiring next month.
    MailApp.sendEmail({ // Send an email with the next month expiration list.
      to: emailRecipient, // Set the email recipient.
      subject: "Items expiring next month", // Set the email subject.
      body: `The following items are expiring next month:\n\n${nextMonthItems.join('\n')}` // Set the email body.
    });
    Logger.log("Next month's expiration email sent."); // Log that the email was sent.
  } else {
    Logger.log("No items are expiring next month."); // Log when there’s nothing to report.
  }
}

/**
 * Identify the form linked to the activeSheet
 */
function identifyLinkedForm() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active Spreadsheet file..
  const sheet = ss.getActiveSheet(); // Gets the active sheet in the file.
  const formUrl = sheet.getFormUrl(); // Returns the URL for the form that sends its responses to this sheet, or null if this spreadsheet has no associated form.
  Logger.log(`The URL of the form linked to this sheet ${formUrl}`); // Logging the /viewform URL of the linked form.
  const form = FormApp.openByUrl(formUrl); // Returns the Form with the specified URL.
  const formId = form.getId(); // Gets the ID of the form.
  Logger.log(`The ID of the form linked to this sheet ${formId}`); // Log the ID of the linked form.
  const formPublishedUrl = form.getPublishedUrl(); // Get the URL used be respondents to the form.
  Logger.log(`The Published URL of the form linked to this sheet ${formPublishedUrl}`); // Log the published URL of the linked form.
  const formTitle = form.getTitle(); // Gets the Form's title.
  Logger.log(`The Title of the form linked to this sheet ${formTitle}`); // Log the Title of the linked form.
}

/** @OnlyCurrentDoc */
function jumptToNamedCell(){
  const cell = "C1"; // Configure the cell to jump to.
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(cell).activate(); // Set the active range.
}
function jumpToReferencedCell(){
  const cell = "C1"; // Configure the cell that contains the address of cell to jump to.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Get the active sheet.
  sheet.setActiveRange(sheet.getRange(sheet.getRange(cell).getValue())); // Set the active range.
}

/** @OnlyCurrentDoc */
function jumpToNamedSheet() {
  const sheetname = "sheetname"; // Configure the sheet name.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname).activate(); // Set the active sheet.
}
function jumpToReferencedSheet(){
  const cell = "C1"; // Configure the cell that contains the name of sheet to jump to.
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // Get the active spreadsheet.
  const sheet = ss.getActiveSheet(); // Get the active sheet.
  ss.setActiveSheet(ss.getSheetByName(sheet.getRange(cell).getValue())); // Set the active sheet.
}

/** START ONOPEN */
function onOpen(){
  menu();
}
/** END ONOPEN */
/** CUSTOM MENU (with Separator & SubMenu) for any user */
function menu(){
  SpreadsheetApp.getUi().createMenu("⚠️Admin Menu⚠️")
    .addItem('Move current sheet to the front', 'moveToFront')
    .addItem('Move current sheet to the back', 'moveToBack')
    .addToUi();
}
/** END CUSTOM MENU */
function moveToFront() {
  SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(1);
}
function moveToBack() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  ss.moveActiveSheet(sheets.length);
}

/**
 * On change - Delete corresponding responses from Form when rows deleted from sheet.
 * Prerequisites: An "On change" installable trigger for the "onChangeRemoveResponses" function.
 */
const firstRowOfData = 2; // Configure the first row of data.

function onChangeRemoveResponses(e) {
  const eventType = e.changeType; // The type of change.
  const ss = e.source; // A Spreadsheet object, representing the Google Sheets file to which the script is container-bound.
  const sheet = ss.getActiveSheet(); // Gets the active sheet in the file.
  if (eventType === "REMOVE_ROW") { // IF e.changeType is 'REMOVE_ROW', then...
    const r = sheet.getActiveRange(); // Returns the selected range in the active sheet, or null if there is no active range.
    const firstRowRemoved = r.getRow(); // Get the number of the first row removed.
    const totalRowsRemoved = r.getNumRows(); // Get the total number of rows removed.
    // Logger.log('First Row Removed: ' + firstRowRemoved); // Logging for troubleshooting.
    // Logger.log('Total Rows Removed: ' + totalRowsRemoved); // Logging for troubleshooting.
    const formUrl = sheet.getFormUrl(); // Returns the URL for the form that sends its responses to this sheet, or null if this sheet has no associated form.
    const form = FormApp.openByUrl(formUrl); // Returns the Form with the specified URL.
    const responses = form.getResponses(); // Gets an array of all of the form's responses.
   // Create a mapping of row numbers to response IDs.
    const rowToResponseIdMap = {};
    for (let i = 0; i < responses.length; i++) {
      const response = responses[i];
      const rowIndex = i + firstRowOfData; // Adjust index to account for header rows.
     rowToResponseIdMap[rowIndex] = response.getId();
    }
    // Determine which responses to delete based on row indices
    for (let i = 0; i < totalRowsRemoved; i++) {
      const rowIndexToDelete = firstRowRemoved + i; // Calculate row index to delete.
      const responseIdToDelete = rowToResponseIdMap[rowIndexToDelete]; 
      if (responseIdToDelete) {
        form.deleteResponse(responseIdToDelete);
        // Logger.log('Removed Response with ID: ' + responseIdToDelete); // Logging for troubleshooting.
      } else {
        Logger.log('No Response found for Row Index: ' + rowIndexToDelete);
      }
    }
  }
}

/**
 * @see https://developers.google.com/apps-script/guides/triggers/events#change
 * e.changeType (for events in functions that have "On change" installed triggers)
 * ▶ EDIT - Any change to cell values, cell borders, cell merges, inserting image in-cell.
 * ▶ FORMAT - Any changes to background colour, font formatting, number format, wrap strategy.
 * ▶ INSERT_ROW - When a new row is added.
 * ▶ REMOVE_ROW - When an existing row is removed.
 * ▶ INSERT_COLUMN - When a new column is added.
 * ▶ REMOVE_COLUMN - When an existing column is removed.
 * ▶ INSERT_GRID - When a new sheet/tab is added.
 * ▶ REMOVE_GRID - When an existing sheet/tab is removed.
 * ▶ OTHER - Renaming a sheet, inserting charts, inserting image over-cells, adding/editing notes, adding/replying to comments, recalculating IMPORTRANGE formulas.
 * ▶▶ ATTENTION: Because formula recalculation causes "OTHER" to be detected, even when no values have changed, you must supplement with other checks so as to prevent the apps script from processing unnecessarily.
 */

/**
 * Log the event object details when a change is made to the spreadsheet.
 *
 * @param {Event} e - The event object containing metadata about the change.
 */
function onChangeLogging(e) {
  console.log(JSON.stringify(e));
}
/**
 * BASIC Logging & email notification when a change is made to the Sheets file.
 * Author: Mr Shane
 * Version: 2025-06-19
 *
 * Prerequisites:
 * - An "On change" installable trigger for the "basicLoggingAndNotification" function.
 * - The function runs in the context of the current document only.
 * - NOTE: Changes made by external Apps Scripts or via API will NOT trigger this function.
 *
 * @param {Event} e - The change event object from the trigger.
 * @OnlyCurrentDoc
 */
function basicLoggingAndNotification(e) {
  const recipient = Session.getEffectiveUser().getEmail(); // Email address of the trigger owner.
  const changeType = e.changeType || "Unknown"; // The type of change.
  const source = e.source ? e.source.getName() : "Unknown source"; // The file name.
  console.log(JSON.stringify(e)); // Log full event object for diagnostics.

  // Build email content
  const subject = `Spreadsheet Change Detected in "${source}"`;
  const body = `A change has been made to your spreadsheet.

                Details:
                - Spreadsheet: ${source}
                - Change Type: ${changeType}
                - Timestamp: ${new Date().toLocaleString()}

                This is an automated message triggered by an installable "On change" trigger.`;

  MailApp.sendEmail(recipient, subject, body); // Send the notification.
}
/**
 * ADVANCED Logging & email notification when a user physically changes something in a Sheets file.
 * To get notifications for the following changeTypes:
 * EDIT, FORMAT, INSERT_ROW, REMOVE_ROW, INSERT_COLUMN
 * Author: Mr Shane
 * Version: 2025-06-19
 *
 * Prerequisites:
 * - An "On change" installable trigger for the "advancedLoggingAndNotification" function.
 * - The function runs in the context of the current document only.
 * - Triggers of any kind don't recognise edits/changes made by external Apps Scripts.
 * - DO NOT leave the trigger configured unnecessarily as it may send a gross amount of email.
 * - getActiveSheet() will return the first sheet of the file, not the sheet where the change occurred.
 *
 * @param {Event} e - The change event object from the trigger.
 * @OnlyCurrentDoc
 */
function advancedLoggingAndNotification(e) {
  const eventType = e.changeType; // The event changeType.
  const ss = e.source; // The Spreadsheet file.
  const r = ss.getActiveRange(); // The range that was changed.
  const firstRow = r.getRow(); // Get the number of the first row.
  const totalRows = r.getNumRows(); // Get the total number of rows.
  const firstCol = r.getColumn(); // Get the number of the first column.
  const totalCols = r.getNumColumns(); // Get the total number of rows.
  const recipientEmail = Session.getEffectiveUser().getEmail(); // The email recipient.
  const subject = `File changeType: ${eventType}`; // The subject line of the email.
  let message = `Change type: ${eventType},`;// The base message for any e.changeType.
  if ( !/OTHER|INSERT_GRID|REMOVE_GRID/.test( eventType ) ) { // Using regex, IF e.changeType is not INSERT_GRID or REMOVE_GRID, then...
    const sheetName = ss.getSheetName(); // Get name of the changed sheet.
    message += `\nSheet name: ${sheetName} ,`; // Append the message with the sheet name.
  };
  if ( eventType === 'EDIT' ) { // IF e.changeType is 'EDIT', then...
    const range = r.getA1Notation(); // Get the range notation.
    const values = r.getValues().flat(); // Get the values in the range.
    message += [`\nRange: ${range}`, // Append the message with the range.
                `\nValues: ${values.join(", ")}`]; // Append the message with the values.
  };
  if ( eventType === 'FORMAT' ) { // IF e.changeType is 'FORMAT', then...
    const backgroundColor = r.getBackgroundColor(); // Get the background colour.
    const fontColor = r.getFontColor(); // Get the font colour.
    const fontFamily = r.getFontFamily(); // Get the font family.
    const fontSize = r.getFontSize(); // Get the font size.
    const fontStyle = r.getFontStyle(); // Get the font style.
    const fontWeight = r.getFontWeight(); // Get the font weight.
    const numberFormat = r.getNumberFormat(); // Get the number format.
    const wrapStrategy = r.getWrapStrategy(); // Get the wrap strategy.
    message += [`\nBackground Colour: ${backgroundColor}`, // Append the message with the background colour.
                `\nFont Color: ${fontColor}`, // Append the message with the font colour.
                `\nFont Family: ${fontFamily}`, // Append the message with the font family.
                `\nFont Size: ${fontSize}`, // Append the message with the font size.
                `\nFont Style: ${fontStyle}`, // Append the message with the font style.
                `\nFont Weight: ${fontWeight}`, // Append the message with the font weight.
                `\nNumber Format: ${numberFormat}`, // Append the message with the number format.
                `\nWrap Strategy: ${wrapStrategy}`]; // Append the message with the wrap strategy.
  };
  if ( eventType === "INSERT_ROW" ) { // IF e.changeType is 'INSERT_ROW', then...
    message += [`\nFirst Row Inserted at: ${firstRow}`, // Append the message with the first row inserted.
                `\nTotal Rows Inserted: ${totalRows}`]; // Append the message with the total number of rows inserted.
  };
  if ( eventType === "REMOVE_ROW" ) { // IF e.changeType is 'REMOVE_ROW', then...
    message += [`\nFirst Row Removed at: ${firstRow}`, // Append the message with the first row removed.
                `\nTotal Rows Removed: ${totalRows}`]; // Append the message with the total number of rows removed.
  };
  if (eventType === "INSERT_COLUMN" ) { // IF e.changeType is 'INSERT_COLUMN', then...
    let columnLetter = "";
    let colNum = firstCol;
    while (colNum > 0) {
      const modulo = (colNum - 1) % 26;
      columnLetter = String.fromCharCode(65 + modulo) + columnLetter; // Create the column letters string
      colNum = Math.floor((colNum - 1) / 26);
    }
    message += [`\nFirst Column Inserted at: ${columnLetter}`, // Append the message with the letter of the first column inserted.
                `\nTotal Columns Inserted: ${totalCols}`]; // Append the message with the total number of columns inserted.
  };
  Logger.log( message ); // Log the details about the change made.
  ss.toast( message ); // Display a toast popup with details about the change made.
  MailApp.sendEmail( recipientEmail, subject, message ); // Send an email with details about the change made.
}

/**
 * setFontColor & setBackgroundColor based on the ActiveUser
 * Prerequisites: "On edit" installable trigger
 */
function getActiveUserFormatting() {
  const user = Session.getActiveUser().getEmail();
  const range = SpreadsheetApp.getActiveRange();
  switch(user){
    case "user1@xxx":range.setFontColor("white").setBackgroundColor("#FF0000");break; // background red
    case "user2@xxx":range.setFontColor("blue").setBackgroundColor("#00FF00");break; // background green
    case "user3@xxx":range.setFontColor("yellow").setBackgroundColor("#0000FF");break; // background blue
    case "user4@xxx":range.setFontColor("red").setBackgroundColor("#00FFFF");break; // background cyan
    case "user5@xxx":range.setFontColor("aqua").setBackgroundColor("#FF00FF");break; //background magenta
    case "user6@xxx":range.setFontColor("blue").setBackgroundColor("#FFFF00");break; // background yellow
    default:range.setFontColor("white").setBackgroundColor("#000000");break; // for all other users, background black
  }
}

/**
 * Backup SUBMITTED response data to other sheet.
 * Prerequisites: Collect email addresses = Verified.
 * Author: Mr Shane
 * Version: 2024-09-06
 */
function onFormSubmit(e) {
  const ss = e.source; // The spreadsheet file.
  const sh = ss.getActiveSheet(); // The sheet linked to the form.
  const shbackup = ss.getSheetByName("Backup"); // The backup sheet.
  const formUrl = sh.getFormUrl(); // Returns the URL for the form that sends its responses to this sheet.
  const form = FormApp.openByUrl(formUrl); // Returns the Form with the specified URL.
  const formResponses = form.getResponses(); // Get all form responses.
  const latestResponse = formResponses[formResponses.length - 1]; // Get the latest form response.
  const timestamp = latestResponse.getTimestamp(); // Get the timestamp of the form response.
  const respondentEmail = latestResponse.getRespondentEmail() || ''; // Get the respondent's email address, handle if it's not available.
  const itemResponses = latestResponse.getItemResponses(); // Get the submitted response data.
  // Map the array of response data.
  const backupValues = [timestamp,respondentEmail,...itemResponses.map(itemResponse => itemResponse.getResponse())];
  shbackup.appendRow(backupValues); // Append the combined values to the backup sheet.
}
/**
 * Backup EDITED response data to other sheet.
 * Prerequisites: Collect email addresses = Verified.
 * Attention: The user value isn't included in e.values of edited responses, so the respondent email address must be retrieved from the edited response in the form.
 * Author: Mr Shane
 * Version: 2024-09-06
 */
function onFormSubmit(e) {
  const ss = e.source; // The spreadsheet file.
  const sh = ss.getActiveSheet(); // The sheet linked to the form.
  const shbackup = ss.getSheetByName("Backup"); // The backup sheet.
  const formUrl = sh.getFormUrl(); // Returns the URL for the form that sends its responses to this sheet.
  const form = FormApp.openByUrl(formUrl); // Returns the Form with the specified URL.
  const formResponses = form.getResponses(); // Get all form responses.
  const latestResponse = formResponses[formResponses.length - 1];   // Get the latest form response, which corresponds to the current submission.
  const respondentEmail = latestResponse.getRespondentEmail(); // Get the respondent's email address.
  const formValues = e.values; // The submitted form values.
  const backupValues = [respondentEmail].concat(formValues); // Combine the email with the form values.
  shbackup.appendRow(backupValues); // Append the email and form values to the backup sheet.
}

/**
 * Automatically close a Google Form after a set number of responses.
 * Prerequisites: "On form submit" installable trigger.
 */
/** EDIT USER CONFIGURED GLOBAL VARIABLES */
const MAX_RESPONSES = 5;
/** END USER CONFIGURED GLOBAL VARIABLES */
function onFormSubmit() {
  const ss = SpreadsheetApp.getActiveSpreadsheet(); // get the Response Sheet
  const sheet = ss.getActiveSheet();  // get the active (Form Responses) Sheet
  const numberOfRows = sheet.getLastRow() - 1; // Get the total number of data rows - 1 for the Header row
  Logger.log(`There are ${numberOfRows} responses. The Form will close upon reaching ${MAX_RESPONSES} responses`);
  if (numberOfRows < MAX_RESPONSES) return;
  Logger.log("The maximum number of responses has been reached");
  const formURL = ss.getFormUrl(); // Get the Url of the Form
  const form = FormApp.openByUrl(formURL); // Open the Form file
  form.setAcceptingResponses(false); // Disable Accepting responses
  Logger.log("The Form is no longer accepting responses");
}

/**
 * Send a notification only when a new form submission is received.
 * This script will NOT send a notification when an existing response is edited.
 * @see https://stackoverflow.com/a/76695898/14678267
 *
 * Prerequisites:
 * - This script must be container-bound to the Google Sheet linked to the Form.
 * - Set up an "On form submit" installable trigger for this function via Apps Script UI.
 *
 * Note: It's not possible to achieve the same effect directly from a Google Form,
 * because a Google Form (with or without a script) cannot distinguish between new and edited submissions.
 * Additionally, Forms do not record or expose any indication of edits within a response,
 * whereas linked Google Sheets add cell notes when existing responses are modified.
 */
function sendFormEmails(e) {
  const notes = e.range.getNotes();
  const hasNotes = notes[0].some(note => note !== '');
  if (!hasNotes) {
    const toAddress = e.values[1]; // Configure the column index (A=0, B=1, etc)
    const subject = "New Form Submission";
    const message = "A new form response was submitted.";
    const fromAddress = "fromemail@emaildomain.com"; // Configure the from email address
    const ccAddress = "ccemail@emaildomain.com"; // Configure the cc email address
    MailApp.sendEmail(toAddress, subject, message, {htmlBody: message, from: fromAddress, cc: ccAddress});
  }
}

/**
 * Prerequisites: Installable Triggers are required when editors will set protection on ranges where only the owner can edit those ranges.
 */
/** GLOBAL CONSTANTS */
 const permittedUser = "emailaddress_goes_here"; // Enter the email address of a specified user that will retain Editor access.
 const currentUser = Session.getEffectiveUser(); // Gets the email address of the current user.
/** END GLOBAL CONSTANTS */
function snippetOnly() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheets()[0]; // assuming you want the first sheet
  const protection1 = sh.getRange(row + 1, 2, 1, 2).protect();
  const protection2 = sh.getRange(row - 1, 4, 1, 3).protect();
  const protection3 = sh.getRange(row    , 9, 1, 4).protect();
  setProtection_(protection1);
  setProtection_(protection2);
  setProtection_(protection3);
}
/**
 * @see https://developers.google.com/apps-script/reference/spreadsheet/protection
 */
function setProtection_(protection) {
  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script throws an exception upon removing the group.
  protection.addEditor(currentUser); // Add the current user as an editor.
  protection.removeEditors(protection.getEditors()); // Remove all other editors from the protected range.
  protection.addEditor(permittedUser); // Grant edit access to the specified permitted user.
  if (protection.canDomainEdit()) { // If has domain-wide edit access, then...
    protection.setDomainEdit(false); // Disable domain-wide edit access.
  }
}
/**
 * Alternative script
 */
function setProtection_(protection) {
  const currentUser = Session.getEffectiveUser();
  const sheet = protection.getRange().getSheet();
  const currentUserCanEdit = sheet.getEditors().some(function(editor) { // Check if the current user has edit access to the sheet
  return editor.getEmail() === currentUser.getEmail();
  });
  if (currentUserCanEdit) {
    protection.addEditor(currentUser); // Add the current user as an editor.
    protection.removeEditors(protection.getEditors()); // Remove all other editors
  } else {
    protection.addEditor(permittedUser); // Grant edit access to the specified permitted user
  }
  if (protection.canDomainEdit()) { // If has domain-wide edit access
    protection.setDomainEdit(false); // Disable domain-wide edit access
  }
}