1

In have an installed trigger for onEdit and an installed trigger for onChange. I log the contents of the event object that is passed in as below

function installedOnChange(e) {
    var me = CalleeName(arguments);
    Logger.log("%s: %s", me, JSON.stringify(e));

(CalleeName is defined as

function CalleeName(a) {
    return a.callee.toString().split(" ")[1].split("(")[0].trim();
}

).

The stringification of the event contains .oldvalue and .value except in the situation where a cell is ^C copied and then ^V pasted into another cell. In that case, neither are given. For example, here we have the onEdit and onChange event data for copying into J19:

onEdit: {"authMode":"FULL","range":{"columnEnd":10,"columnStart":10,"rowEnd":19,"rowStart":19},"source":{},"triggerUid":"3504192","user":{"email":"data@blahblahblah","nickname":"data"}}

onChange: {"authMode":"FULL","changeType":"EDIT","source":{},"triggerUid":"3538611","user":{"email":"data@blahblahblah","nickname":"data"}}

As you can see, no .value and no .oldvalue.

I can equivalence the .value by evaluating e.source.getActiveCell().getValue() however, I cannot -- thus far -- establish what the previous value of the cell was, that is, what the destination cell held before the ^V paste was issued.

It occurred to me that perhaps this value might be available in the Show Edit History, a list available by right-clicking a cell in the sheet. However, I cannot find a programmatic interface to that information. Does such a thing exist? If so, how? and if not, where do I post a change request?

bugmagnet
  • 7,631
  • 8
  • 69
  • 131
  • 1
    Unfortunately, in the current stage, there are no built-in methods for directly retrieving the history of each cell in Spreadsheet service of Google Apps Script. So as several workarounds, how about using the revision data? [Ref](https://stackoverflow.com/q/57387113/7108653) Or how about using a temporal Spreadsheet saving the old values? [Ref](https://gist.github.com/tanaikech/73edaed1268a6d07118aed538aa5608d) But if this was not the direction you expect, I apologize. – Tanaike Apr 03 '20 at 05:47
  • These are interesting ideas. However, I fear that speed of processing may not be on my side were I to implement them. But thank you for your interest. – bugmagnet Apr 03 '20 at 12:40
  • Thank you for replying. If my proposals were not useful for your situation, I apologize. – Tanaike Apr 04 '20 at 00:46
  • 1
    @Tanaike and what about other scripting systems? (Python, PHP, ... do have gsheet api interfaces). Do you know if google sheet CELL history is available from any of them? Thanks – abu May 01 '21 at 19:59

0 Answers0