Google Sheets undo/redo function

• You want to achieve the functions for undo and redo for the Google Spreadsheet.
• You can use Google Apps Script.

I could understand like above. For this, how about this sample script? So please think of this as just one of several possible answers.

Usage:

Please do the following flow for using the sample script.

1. Create new Spreadsheet and open the script editor.
   • If the 1st tab is not the name of Sheet1, please change it to Sheet1. In this sample script, as a test situation, the undo and redo functions for running to the sheet of Sheet1 in the active Spreadsheet. Please be careful this.
2. Copy and paste the following sample script to the script editor, and save the script.
3. Install the installable OnEdit event trigger to the function installedOnEdit.
   • You can see the document for doing this at here. Please set installedOnEdit as OnEdit event trigger.
   • When you install the installable OnEdit event trigger, the authorization screen is opened. So please authorize the scopes.
4. Please close the Spreadsheet and reopen the Spreadsheet. By this, onOpen() is run by the simple trigger. And offset is cleared. And then, please open the script editor.
5. At the Spreadsheet, put manually "foo" to the cell "A1".
   • By this, the Spreadsheet of logSpreadsheet is created to the root folder. This is used as the log.
   • So only this one time, please wait for creating the Spreadsheet.
6. After you confirmed the created Spreadsheet at the root folder, put manually "bar" to the cell "A1".

7. Put manually "baz" to the cell "A1".

   • At this time, when you see the log Spreadsheet, you can see the following values to the cell "A1:A3". This is used for undo and redo function.

     {"range":"A1","value":"foo","oldValue":""}
     {"range":"A1","value":"bar","oldValue":"foo"}
     {"range":"A1","value":"baz","oldValue":"bar"}
     

8. Run the function undo at the script editor.

   • By this, the value of cell "A1" is changed from baz to bar.
9. Run the function undo at the script editor.
   • By this, the value of cell "A1" is changed from bar to foo.
10. Run the function undo at the script editor.
    • By this, the value of cell "A1" is changed from foo to the empty. Because the initial situation is the empty.
11. Run the function redo at the script editor.
    • By this, the value of cell "A1" is changed from foo to bar.
12. Run the function redo at the script editor.
    • By this, the value of cell "A1" is changed from bar to baz.
13. Run the function redo at the script editor.
    • By this, the value of cell "A1" is not changed. Because this situation is the latest one.

Sample script:

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var prop = PropertiesService.getScriptProperties();

function onOpen() {
  prop.setProperty("offset", "0");
}

function getSheet() {
  var logId = prop.getProperty("logSpreadsheetId");
  if (logId) {
    return SpreadsheetApp.openById(logId).getSheets()[0];
  }
  throw new Error("Please edit a cell in the Spreadsheet. By this, the Spreadsheet for logging is created.");
}

// Undo function
function undo() {
  var logsheet = getSheet();
  var offset = Number(prop.getProperty("offset"));
  var lastRow = logsheet.getLastRow();
  if (lastRow + offset > 0) {
    var log = logsheet.getRange(lastRow, 1).offset(offset, 0).getValue();
    var obj = JSON.parse(log);
    sheet.getRange(obj.range).setValue(obj.oldValue);
    prop.setProperty("offset", offset - 1);
  }
}

// Redo function
function redo() {
  var logsheet = getSheet();
  var offset = Number(prop.getProperty("offset")) + 1;
  var lastRow = logsheet.getLastRow();
  if (lastRow + offset <= lastRow) {
    var log = logsheet.getRange(lastRow, 1).offset(offset, 0).getValue();
    var obj = JSON.parse(log);
    sheet.getRange(obj.range).setValue(obj.value);
    prop.setProperty("offset", offset);
  }
}

// Please set the installable OnEdit event trigger to this function.
function installedOnEdit(e) {
  var logId = prop.getProperty("logSpreadsheetId")
  var ss;
  if (logId) {
    ss = SpreadsheetApp.openById(logId);
  } else {
    ss = SpreadsheetApp.create("logSpreadsheet");
    prop.setProperty("logSpreadsheetId", ss.getId());
  }
  var log = {range: e.range.getA1Notation(), value: "value" in e ? e.value : "", oldValue: "oldValue" in e ? e.oldValue : ""};
  ss.getSheets()[0].appendRow([JSON.stringify(log)]);
}

Note:

• This is a simple sample script. Unfortunately, the complicated actions might not be able to be used. For example, in the current script cannot use to edit the multiple cells. I deeply apologize for this. When you want to add more complicated actions, it is required to modify the script for each situation. Please be careful this.
• In the current stage, when the cell value is deleted by clicking the delete button, the old value is not included in the event object. So when you want to test the delete of value, please click the cell and delete each character in the cell by the backspace button. By this, the old value is included in the event object. Please be careful this.
• I think that if you want to make more complicated actions, to overwrite the Spreadsheet with the revision data might be suitable. Ref

Reference:

• Installable Triggers