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

问题描述

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

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

而且我已经在上面设置了通知，以便在单元格发生变化时立即通过电子邮件发送给我.

我通过电子表格 API 对该电子表格进行了更改 - 即不是手动更改.

然后我收到一封这样的电子邮件:

<块引用>

主题:通知测试"最近被编辑

查看您的 Google 文档通知测试"中的更改:点击这里

其他人从 10/01/2014 12:23 到 12:23(格林威治平均时间)

• 值已更改

如果我打开单击此处"链接，则会得到这个 URL，其中显示了电子表格中已更改的单元格:

https://docs.google.com/a/DOMAINGOESHERE/spreadsheet/ver?key=tn9EJJrk6KnJrAEFaHI8E3w&t=1389356641198000&pt=13893566211980000&t=13893566211980000&bjH&bKiGyapsE&AJamp;difiXuyapxE&B&bHyapxE&AJamp;>

我的问题是:

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

我浏览了 Google 电子表格 API:https://developers.google.com/google-apps/spreadsheets/>

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

我还尝试使用 Google Apps 脚本设置 onEdit() 事件:https://developer.google.com/apps-script/understanding_triggers

我认为最后一种方法就是答案.

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

有什么想法吗?

解决方案

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

var sheet = **whatever**;//您将要进行更改的电子表格var range = **whatever**;//您将检查更改的范围var compSheet = **whatever**;//您将与更改进行比较的工作表函数 checkMatch(){var myCurrent = sheet.getRange(range).getValues();var myComparison = compSheet.getRange(range).getvalues();if(myCurrent == myComparison){//检查是否有差异for(i=0;i<compSheet.length;++i){//由于getValues返回的是'多维'数组，所以使用2个for循环比较每个元素for(j=0;j

然后从资源>当前项目的触发器，您可以将checkMatch设置为每分钟运行一次.

另请查看https://developers.google.com/gdata/samples/spreadsheet_sample 用于将数据提取为 json

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?

解决方案

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

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