Change cell color if data is changed from "initial value" in Google Sheets?

Question

How can I architect a system where a customer is given a spreadsheet with 1000 rows / 5-columns each of data, and if they edit any cell, it gets highlighted blue? Probably doing this using App scripts somehow, but I am new to that having only made on app script in JS for sorting data.

Basically, I would like to send a customer a spreadsheet filled out with data, either as an xlsx file, or a Google Sheet URL (can you do either or only one approach?). Ideally it would be an XLSX file, so they can simply press "export data" and get the data as an excel file. Then they open the file in Google Sheets (as recommended in our docs), and they are off to editing / cleaning the data. For any cell they change, it changes color to blue, so they can get a sense of the changes they have made.

Is this at all possible? If so, what is the basics of the app script for such a spreadsheet? Given the basics, I can flesh it out for my particular needs I guess.

What I'm imagining is, onsheetload of some sort, clone all the records in the sheet. Then oncellchange, diff with the record[cell], and if it has changed, highlight the cell blue. Things I am not aware of yet:

• The events/handlers to add (sheet load, and cell change).
• If they sort it, it shouldn't mean the cells are considered "changed".
• How to change the cell color.

Any help would be appreciated. Not even sure if this sort of thing is possible at this stage.

Answer 1

Try an onEdit(e) function simple trigger, like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  setColorLightBlue_(e);
}


/**
* Sets cell fill color to light blue when a cell is manually edited.
*
* @param {Object} e The onEdit() event object.
*/
function setColorLightBlue_(e) {
  if (e.range) e.range.setBackground('#cfe2f3'); // light blue 3
}

Fill colors will sort along the data.