email - Get details of cells changed from a Google Spreadsheet change notification in a machine readable format

Question

If I have a Google Spreadsheet e.g.

https://docs.google.com/spreadsheet/ccc?key=0AjAdgux-AqYvdE01Ni1pSTJuZm5YVkJIbl9hZ21PN2c&usp=sharing

And I have set up notifications on it to email me immediately whenever a cell changes.

And I make a change to that spreadsheet via the spreadsheet API - i.e. not by hand.

Then I get an email like this:

Subject: "Notification Test" was edited recently

See the changes in your Google Document "Notification Test": Click here

other person made changes from 10/01/2014 12:23 to 12:23 (Greenwich Mean Time)

• Values changed

If I open the 'Click here' link then I get this URL which shows me the cell that has changed in the spreadsheet:

https://docs.google.com/a/DOMAINGOESHERE/spreadsheet/ver?key=tn9EJJrk6KnJrAEFaHI8E3w&t=1389356641198000&pt=1389356621198000&diffWidget=true&s=AJVazbUOm5tHikrxX-bQ0oK_XEapjEUb-g

My question is:

Is there a way to get the information about which cell has changed in a format that I can work with programmatically- e.g. JSON?

I have looked through the Google Spreadsheet API: https://developers.google.com/google-apps/spreadsheets/

and at the Drive API Revisions: https://developers.google.com/drive/manage-revisions

I have also tried setting up an onEdit() event using Google Apps Script: https://developers.google.com/apps-script/understanding_triggers

I thought this last approach would be the answer.

The problem with this approach is that whilst onEdit can be used to email details of changes, it appears to only be fired if the spreadsheet is edited by hand whereas mine is being updated programmatically via the spreadsheet API.

Any ideas?

Answer 1

You could build a function that checks for changes. One way to do this is by comparing multiple instances of the same spreadsheet. If there are differences, you could email yourself. Using the time driven trigger, you can check every minute, hour, day, or week (depending on your needs).

var sheet = **whatever**;//The spreadsheet where you will be making changes
var range = **whatever**;//The range that you will be checking for changes
var compSheet = **whatever**;//The sheet that you will compare with for changes
function checkMatch(){
  var myCurrent = sheet.getRange(range).getValues();
  var myComparison = compSheet.getRange(range).getvalues();
  if(myCurrent == myComparison){//Checks to see if there are any differences
    for(i=0;i<compSheet.length;++i){ //Since getValues returns a 'multi-dimensional' array, 2 for loops are used to compare each element
     for(j=0;j<compSheet[i].length;++i){
      if(myCurrent[i][j] != myComparison[i][j]){//Determines if there is a difference;
       //***Whatever you want to do with the differences, put them here***
     }
    }

    myEmailer(sheet.getUrl());//Passes the url of sheet to youur emailer function 
    compSheet.getRange(range).setValues(myCurrent);//Updates compSheet so that next time is can check for the next series of changes
    }
  }

Then from Resources>Current project's triggers you can set checkMatch to run every minute.

Also check out https://developers.google.com/gdata/samples/spreadsheet_sample for pulling data as json