0

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.

1
  • One possible strategy is to create a replica of the spreadsheet on a new tab, and then conditional format every cell of the original spreadsheet so that it is blue if different than the replica. This, of course, won't accomodate the 'no blue if sorted' requirement.
    – Oren
    Sep 12, 2023 at 10:55

1 Answer 1

2

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.

2
  • Great! Is it possible to pass this along with the file somehow? So they can download the xlsx file and upload it to google sheets, and vualah, they have the blue-highlight-on-dirty-cell feature? If not, what is the best approach for that to happen?
    – Lance
    Sep 12, 2023 at 13:57
  • The script will be bound to the Google Sheet and will get copied automatically when you File > Make a copy or use /template/preview instead of /edit in the share URL. Scripts only work in Google Sheets and not in .xlsx files. Please ask only one question per post. Post a new question instead. Sep 12, 2023 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.