从Google电子表格更改通知以机器可读格式获取更改单元格的详细信息 [英] Get details of cells changed from a Google Spreadsheet change notification in a machine readable format

问题描述

如果我有Google电子表格，例如

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.

我通过电子表格API更改了该电子表格 - 即不是

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

查看您的Google文档通知测试中的更改：点击
此处

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

其他人从10/01/2014 12:23至12:23（格林威治
平均时间）更改

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

• 值更改

如果我打开点击此处链接，那么我得到这个网址，显示电子表格中已更改的单元格：

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& pt = 1389356621198000& pt = true& s = AJVazbUOm5tHikrxX-bQ0oK_XEapjEUb-g

我的问题是：

有没有办法获取有关哪个单元格已更改的信息，我可以使用编程方式进行更改 - 例如JSON？

我查看了Google电子表格API：
https://developers.google.com/google-apps/spreadsheets/

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

并在Drive API修订版本：
https://developers.google.com/drive/manage-revisions

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

我还尝试使用Google Apps脚本设置onEdit（）事件： https://developers.google.com/apps-script/understanding_triggers

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.

这种方法的问题是，虽然onEdit可用于发送更改的详细信息，但似乎只有在电子表格被编辑时才会触发而我正在通过电子表格API以编程方式更新。

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.

任何想法？

推荐答案

您可以构建一个检查更改的函数。一种方法是通过比较同一电子表格的多个实例。如果有差异，你可以给自己发电子邮件。使用时间驱动的触发器，您可以检查每分钟，小时，日或周（取决于您的需要）。

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
    }
  }

然后从资源>当前项目的触发器设置 checkMatch 每分钟运行。

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

另请查看 https://developers.google.com/gdata/samples/spreadsheet_sample 将数据拉为json

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

这篇关于从Google电子表格更改通知以机器可读格式获取更改单元格的详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！