英文:

Google App Script: Send hourly email with spreadsheet changes, if any

问题 {#heading}

我收到一个请求，要求编写一个脚本，每当特定的Google电子表格发生更改时，就发送一封电子邮件。由于可能会进行重复的编辑，电子邮件应该每小时仅发送一次，并且应该包含更改的内容。

用例：A团队维护一个电子表格，比如产品列表。B团队希望在A团队对电子表格进行更改时收到通知。

下面的代码将在每次进行更改时发送一封电子邮件：

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = e.source.getActiveSheet();
  const cellChange = {
    name:  ss.getName(),
    url:   ss.getUrl(),
    sheet: sheet.getName(),
    cell:  e.range.getA1Notation(),
    from:  e.oldValue || '(multiple changes)',
    to:    e.value || '(multiple changes)'
  };
  const subject = `Spreadsheet ${cellChange.name} has been updated`;
  const body = `- Sheet: ${cellChange.sheet}
- Cell: ${cellChange.cell}
- From: ${cellChange.from}
- To:   ${cellChange.to}
- URL:  ${cellChange.url}
`;
  const email = {
    to:      'jimmy.neutron@example.com',
    subject: subject,
    body:    body
  }
  console.log('send email:', email);
  MailApp.sendEmail(email);
}

这太喧闹了。我将发布一个我想出的解决方案的答案。 英文:

I got a request to come up with a script that sends an email whenever changes are done to a specific Google spreadsheet. Since repeated edits can be done, emails should only be sent once an hour, and should contain the changes.

Use case: Team A maintains a spreadsheet, such as a product list. Team B would like to get notified if team A made changes to the spreadsheet.

This here will send an email every time a change is done:

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = e.source.getActiveSheet();
  const cellChange = {
    name:  ss.getName(),
    url:   ss.getUrl(),
    sheet: sheet.getName(),
    cell:  e.range.getA1Notation(),
    from:  e.oldValue || '(multiple changes)',
    to:    e.value || '(multiple changes)'
  };
  const subject = `Spreadsheet ${cellChange.name} has been updated`;
  const body = `- Sheet: ${cellChange.sheet}
- Cell: ${cellChange.cell}
- From: ${cellChange.from}
- To:   ${cellChange.to}
- URL:  ${cellChange.url}
`;
  const email = {
    to:      'jimmy.neutron@example.com',
    subject: subject,
    body:    body
  }
  console.log('send email:', email);
  MailApp.sendEmail(email);
}

This is too noisy. I'll post an answer with the solution I came up with.

答案1 {#1}

得分: 0

以下是Google Apps Script代码，已翻译为中文：

/* 设置更改通知电子邮件：
 * 1. 复制下面的代码到任何电子表格的脚本中
 * 2. 在编辑器中，根据需要调整下面的 notifyChangesConf
 * 3. 在编辑器中，在Debug旁边的顶部，选择函数 notifyChangesSendEmail，并运行它一次
 *    - 接受运行脚本的权限请求
 * 4. 在触发器中，安装一个时间驱动的触发器：
 *    - 函数：notifyChangesSendEmail
 *    - 事件源：时间驱动
 *    - 类型：分钟定时器
 *    - 分钟间隔：每5分钟
 * 5. 在触发器中，安装一个在编辑时触发器：
 *    - 函数：notifyChangesOnEdit
 *    - 事件源：来自电子表格
 *    - 事件类型：在编辑时
 */
const notifyChangesConf = {
  exclude:    [ 'Scratch', 'Sheet8' ], // 排除通知的表格
  idle:       60, // 发送电子邮件之前的空闲时间（分钟）
  email: {    // 详见 https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage
    to:       'jimmy.neutron@example.com',  // 逗号分隔的电子邮件列表
    subject:  '电子表格更新：%NAME% > %SHEET%',
    body:
`电子表格 %NAME% 已被修改：
%REPEAT%
• %SHEET%!%CELL%: %TO%%REPEAT%

• 网址：%URL%
`,
    noReply:  true
  }
};

function notifyChangesSendEmail(e) {

  function expand_(str) {
    const parts = str.split(/%REPEAT%/);
    if(parts[1]) {
      parts[1] = changes.map(obj => {
        return parts[1]
        .replace(/%SHEET%/g,  obj.sheet)
        .replace(/%CELL%/g,   obj.cell)
        .replace(/%FROM%/g,   obj.from || '(多个单元格更改)')
        .replace(/%TO%/g,     obj.to || '(多个单元格更改)');
      }).join('')
    }
    return parts.join('')
    .replace(/%NAME%/g,   ss.getName())
    .replace(/%URL%/g,    ss.getUrl())
    .replace(/%SHEET%/g,  changes[0].sheet)
    .replace(/%CELL%/g,   changes[0].cell)
    .replace(/%FROM%/g,   changes[0].from || '(多个单元格更改)')
    .replace(/%TO%/g,     changes[0].to || '(多个单元格更改)');
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let changes = notifyChangesCacheRead();
  if(changes.length) {
    const lastUpdate = changes[changes.length - 1].time;
    const now = new Date().valueOf();
    const diff = Math.round(notifyChangesConf.idle - (now - lastUpdate) / (1000 * 60));
    if(diff <= 0) {
      notifyChangesCacheDelete();
      const email = JSON.parse(JSON.stringify(notifyChangesConf.email));
      email.subject = expand_(email.subject);
      email.body = expand_(email.body);
      console.log('notifyChangesSendEmail data:', email);
      MailApp.sendEmail(email);
    } else {
      console.log('notifyChangesSendEmail: ' + changes.length + ' 个更改已存在，' +
                  diff + ' 分钟内剩余 ' + notifyChangesConf.idle + ' 分钟的空闲时间');
    }
  } else {
    console.log('notifyChangesSendEmail: 没有更改，无操作');
  }
}

function notifyChangesOnEdit(e) {
  const sheet = e.source.getActiveSheet();
  const sheetName = sheet.getName();
  if(notifyChangesConf.exclude.indexOf(sheetName) >= 0) {
    console.log('notifyChangesOnEdit: 无操作，' + sheetName + ' 在排除列表中');
  } else {
    let changes = notifyChangesCacheRead();
    let now = new Date().valueOf();
    const obj = {
      sheet:  sheetName,
      cell:   e.range.getA1Notation(),
      from:   e.oldValue,
      to:     e.value,
      time:   now
    };
    console.log('notifyChangesOnEdit 更改：', obj);
    changes.push(obj);
    notifyChangesCacheSave(changes);
  }
}

function notifyChangesCacheRead() {
  let changes = [];
  let json = CacheService.getScriptCache().get('edit-changes') || '[]';
  try {
    changes = JSON.parse(json);
  } catch(err) {
    console.log('解析缓存更改时出错：',err);
  }
  //console.log('读取缓存', changes);
  return changes;
}

function notifyChangesCacheSave(changes) {
  //console.log('保存缓存', changes);
  CacheService.getScriptCache().put('edit-changes', JSON.stringify(changes), notifyChangesConf.idle * 60 * 2);
}

function notifyChangesCacheDelete() {
  //console.log('删除缓存');
  CacheService.getScriptCache().remove('edit-changes');
}

// 结束

请注意，代码中的注释和配置信息并未翻译，因为您提到不需要翻译代码部分。如果需要任何其他翻译，请告诉我。 英文:

The onEdit() event fires off on each cell change, so we need to first collect and store recent changes, then periodically send an email with the changes.

It is possible to store changes using script properties (getScriptProperties()) or cache service (CacheService.getScriptCache()). The latter is better suited for this use case because it expires automatically, and because it has a larger quota size.

Here is the Google Apps Script code I came up with. It can be configured:

• exclude: exclude sheets from notifications

• idle: idle time in minutes before sending email

• email: email message template, see https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage

  /* Send email notification of changes setup:

  • 1. Duplicate below code to the script of any spreadsheet
  • 2. In Editor, tweak notifyChangesConf below as desired
  • 3. In Editor on top next to Debug, select function notifyChangesSendEmail, and run it once
  • • Accept the permission request to run the script
  • 4. In Triggers, install a time-driven trigger:
  • • Function: notifyChangesSendEmail
  • • Event source: Time-driven
  • • Type: Minutes timer
  • • Minute interval: Every 5 minutes
  • 5. In Triggers, install an on-edit trigger:
  • • Function: notifyChangesOnEdit
  • • Event source: From spreadsheet
  • • Event type: On edit */ const notifyChangesConf = { exclude: [ 'Scratch', 'Sheet8' ], // exclude sheets from notifications idle: 60, // idle time in minutes before sending email email: { // see https://developers.google.com/apps-script/reference/mail/mail-app#sendemailmessage to: 'jimmy.neutron@example.com', // comma list subject: 'Spreadsheet Update: %NAME% > %SHEET%', body: Spreadsheet %NAME% has been modified: %REPEAT% • %SHEET%!%CELL%: %TO%%REPEAT% • URL: %URL%, noReply: true } }; function notifyChangesSendEmail(e) { function expand_(str) { const parts = str.split(/%REPEAT%/); if(parts[1]) { parts[1] = changes.map(obj => { return parts[1] .replace(/%SHEET%/g, obj.sheet) .replace(/%CELL%/g, obj.cell) .replace(/%FROM%/g, obj.from || '(multiple cell changes)') .replace(/%TO%/g, obj.to || '(multiple cell changes)'); }).join('') } return parts.join('') .replace(/%NAME%/g, ss.getName()) .replace(/%URL%/g, ss.getUrl()) .replace(/%SHEET%/g, changes[0].sheet) .replace(/%CELL%/g, changes[0].cell) .replace(/%FROM%/g, changes[0].from || '(multiple cell changes)') .replace(/%TO%/g, changes[0].too || '(multiple cell changes)'); } const ss = SpreadsheetApp.getActiveSpreadsheet(); let changes = notifyChangesCacheRead(); if(changes.length) { const lastUpdate = changes[changes.length - 1].time; const now = new Date().valueOf(); const diff = Math.round(notifyChangesConf.idle - (now - lastUpdate) / (1000 * 60)); if(diff <= 0) { notifyChangesCacheDelete(); const email = JSON.parse(JSON.stringify(notifyChangesConf.email)); email.subject = expand_(email.subject); email.body = expand_(email.body); console.log('notifyChangesSendEmail data:', email); MailApp.sendEmail(email); } else { console.log('notifyChangesSendEmail: ' + changes.length + ' changes exist,' + diff + ' min remaining within ' + notifyChangesConf.idle + ' min idle time'); } } else { console.log('notifyChangesSendEmail: no changes, no action'); } } function notifyChangesOnEdit(e) { const sheet = e.source.getActiveSheet(); const sheetName = sheet.getName(); if(notifyChangesConf.exclude.indexOf(sheetName) >= 0) { console.log('notifyChangesOnEdit: no action, ' + sheetName + ' is in exclude list'); } else { let changes = notifyChangesCacheRead(); let now = new Date().valueOf(); const obj = { sheet: sheetName, cell: e.range.getA1Notation(), from: e.oldValue, to: e.value, time: now }; console.log('notifyChangesOnEdit change:', obj); changes.push(obj); notifyChangesCacheSave(changes); } } function notifyChangesCacheRead() { let changes = []; let json = CacheService.getScriptCache().get('edit-changes') || '[]'; try { changes = JSON.parse(json); } catch(err) { console.log('error parse cached changes:',err); } //console.log('read cache', changes); return changes; } function notifyChangesCacheSave(changes) { //console.log('save cache', changes); CacheService.getScriptCache().put('edit-changes', JSON.stringify(changes), notifyChangesConf.idle * 60 * 2); } function notifyChangesCacheDelete() { //console.log('delete cache'); CacheService.getScriptCache().remove('edit-changes'); } // EOF

Two triggers need to be installed -- see comment section at the beginning of the code.

Limitations:

• pasting a range of cells triggers a notification with note (multiple cell changes) instead of the actual changes
• no change notification is sent if a sheet is changed programmatically by a script

Sample notification email received with above settings:

From: <jimmy.neutron@example.com>
Date: Wed, Aug 9, 2023 at 5:04 PM
Subject: Spreadsheet Update: My sandbox > Sheet10
To: <jimmy.neutron@example.com>
Spreadsheet My sandbox has been modified:
• Sheet10!C6: foo 1
• Sheet10!C7: moo moo 2
• Sheet10!B2: blah blah 3
• Sheet10!B3: foo 4
• Sheet10!B4: (multiple cell changes)
• Sheet10!C5: moo 6
• Sheet10!C6: foo 7
• URL:  https://...

答案2 {#2}

得分: 0

尝试这个：

function onMyEdit(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast('Entry')
  const sh = e.range.getSheet();
  const excl = ['Sheet2', 'Sheet1'];//排除的工作表
  const idx = excl.indexOf(sh.getName());
  if (!~idx) {
    //e.source.toast("Gate1");
    let vs = sh.getRange(e.range.rowStart, e.range.columnStart, e.range.rowEnd - e.range.rowStart + 1, e.range.columnEnd - e.range.columnStart + 1).getDisplayValues();
    let chg = { name: e.source.getName(), url: e.source.getUrl(), sheet: sh.getName(), row: e.range.rowStart, col: e.range.columnStart, from: e.oldValue, to: e.value, rgA1: e.range.getA1Notation(), w: e.range.columnEnd - e.range.columnStart + 1, h: e.range.columnEnd - e.range.columnStart + 1, values: [] }
    vs.forEach((r, i) => {
      let row = [];
      r.forEach((c, j) => {
        row.push(c)
      })
      chg.values.push(row);
    });
    const subject = `电子表格 ${chg.name} 已更新`;
    const body = `- 工作表: ${chg.sheet}
- 范围: ${chg.rgA1}
- 行列(上左): ${chg.row},${chg.col}
- 宽度: ${chg.w}
- 高度: ${chg.h}
- 从: ${e.oldValue ? chg.from : ''}
- 到:   ${e.value ? chg.to : ''}
- 网址:  ${chg.url}
- 值: ${JSON.stringify(chg.values)}
`;
    const email = {
      to: gobj.globals.emailpriv,
      subject: subject,
      body: body
    }
    //console.log('发送电子邮件:', email);
    //MailApp.sendEmail(email);
    GmailApp.createDraft(gobj.globals.emailpriv,subject,body);
  }
}

每小时调用此函数：

```javascript
function sendAllDrafts() {
  const drafts = GmailApp.getDrafts();
  drafts.forEach(d => d.send());
}

这是一种将所有草稿ID存储在数组中的方法，以防您有其他不想丢失的草稿：

function onMyEdit(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast('Entry')
  const sh = e.range.getSheet();
  const excl = ['Sheet2', 'Sheet1'];//排除的工作表
  const idx = excl.indexOf(sh.getName());
  if (!~idx) {
    //e.source.toast("Gate1");
    let vs = sh.getRange(e.range.rowStart, e.range.columnStart, e.range.rowEnd - e.range.rowStart + 1, e.range.columnEnd - e.range.columnStart + 1).getDisplayValues();
    let chg = { name: e.source.getName(), url: e.source.getUrl(), sheet: sh.getName(), row: e.range.rowStart, col: e.range.columnStart, from: e.oldValue, to: e.value, rgA1: e.range.getA1Notation(), w: e.range.columnEnd - e.range.columnStart + 1, h: e.range.columnEnd - e.range.columnStart + 1, values: [] }
    vs.forEach((r, i) => {
      let row = [];
      r.forEach((c, j) => {
        row.push(c)
      })
      chg.values.push(row);
    });
    const subject = `电子表格 ${chg.name} 已更新`;
    const body = `- 工作表: ${chg.sheet}
- 范围: ${chg.rgA1}
- 行列(上左): ${chg.row},${chg.col}
- 宽度: ${chg.w}
- 高度: ${chg.h}
- 从: ${e.oldValue ? chg.from : ''}
- 到:   ${e.value ? chg.to : ''}
- 网址:  ${chg.url}
- 值: ${JSON.stringify(chg.values)}
`;
    const email = {
      to: gobj.globals.emailpriv,
      subject: subject,
      body: body
    }
    //console.log('发送电子邮件:', email);
    //MailApp.sendEmail(email);
    let d = GmailApp.createDraft(gobj.globals.emailpriv, subject, body);
    let ps = PropertiesService.getScriptProperties();
    let idA = JSON.parse(ps.getProperty('drafts')) || [];
    idA.push(d.getId());
    ps.setProperty('drafts',JSON.stringify(idA));
  }
}

function sendAllDrafts() {
  const drafts = GmailApp.getDrafts();
  let ps = PropertiesService.getScriptProperties();
  let idA = JSON.parse(ps.getProperty('drafts'));
  drafts.forEach(d => {
    let idx = idA.indexOf(d.getId());
    if (idx !== -1) {
      d.send();
    }
  });
  ps.setProperty('drafts','[]');
}

注意：我没有完全调试最后一个函数。 英文:

Try this:

function onMyEdit(e) {
//Logger.log(JSON.stringify(e));
//e.source.toast('Entry')
const sh = e.range.getSheet();
const excl = ['Sheet2', 'Sheet1'];//excluded sheets
const idx = excl.indexOf(sh.getName());
if (!~idx) {
//e.source.toast("Gate1");
let vs = sh.getRange(e.range.rowStart, e.range.columnStart, e.range.rowEnd - e.range.rowStart + 1, e.range.columnEnd - e.range.columnStart + 1).getDisplayValues();
let chg = { name: e.source.getName(), url: e.source.getUrl(), sheet: sh.getName(), row: e.range.rowStart, col: e.range.columnStart, from: e.oldValue, to: e.value, rgA1: e.range.getA1Notation(), w: e.range.columnEnd - e.range.columnStart + 1, h: e.range.columnEnd - e.range.columnStart + 1, values: [] }
vs.forEach((r, i) => {
let row = [];
r.forEach((c, j) => {
row.push(c)
})
chg.values.push(row);
});
const subject = `Spreadsheet ${chg.name} has been updated`;
const body = `- Sheet: ${chg.sheet}
- Range: ${chg.rgA1}
- UL(row,col): ${chg.row},${chg.col}
- Width: ${chg.w}
- Length: ${chg.h}
- From: ${e.oldValue ? chg.from : ''}
- To:   ${e.value ? chg.to : ''}
- url:  ${chg.url}
- values: ${JSON.stringify(chg.values)}
`;
const email = {
to: gobj.globals.emailpriv,
subject: subject,
body: body
}
//console.log('send email:', email);
//MailApp.sendEmail(email);
GmailApp.createDraft(gobj.globals.emailpriv,subject,body);
}
}

Call this function once per hour

function sendAllDrafts() {
const drafts = GmailApp.getDrafts();
drafts.forEach(d => d.send());
}

Here's away to store all of the draft ids in an array in case you have other drafts that you don't want to lose.

function onMyEdit(e) {
//Logger.log(JSON.stringify(e));
//e.source.toast('Entry')
const sh = e.range.getSheet();
const excl = ['Sheet2', 'Sheet1'];//excluded sheets
const idx = excl.indexOf(sh.getName());
if (!~idx) {
//e.source.toast("Gate1");
let vs = sh.getRange(e.range.rowStart, e.range.columnStart, e.range.rowEnd - e.range.rowStart + 1, e.range.columnEnd - e.range.columnStart + 1).getDisplayValues();
let chg = { name: e.source.getName(), url: e.source.getUrl(), sheet: sh.getName(), row: e.range.rowStart, col: e.range.columnStart, from: e.oldValue, to: e.value, rgA1: e.range.getA1Notation(), w: e.range.columnEnd - e.range.columnStart + 1, h: e.range.columnEnd - e.range.columnStart + 1, values: [] }
vs.forEach((r, i) => {
let row = [];
r.forEach((c, j) => {
row.push(c)
})
chg.values.push(row);
});
const subject = `Spreadsheet ${chg.name} has been updated`;
const body = `- Sheet: ${chg.sheet}
- Range: ${chg.rgA1}
- UL(row,col): ${chg.row},${chg.col}
- Width: ${chg.w}
- Length: ${chg.h}
- From: ${e.oldValue ? chg.from : ''}
- To:   ${e.value ? chg.to : ''}
- url:  ${chg.url}
- values: ${JSON.stringify(chg.values)}
`;
const email = {
to: gobj.globals.emailpriv,
subject: subject,
body: body
}
//console.log('send email:', email);
//MailApp.sendEmail(email);
let d = GmailApp.createDraft(gobj.globals.emailpriv, subject, body);
let ps = PropertiesService.getScriptProperties();
let idA = JSON.parse(ps.getProperty('drafts')) || [];
idA.push(d.getId());
ps.setProperty('draft',JSON.stringify(idA));
}
}
function sendAllDrafts() {
const drafts = GmailApp.getDrafts();
let ps = PropertiesService.getScriptProperties();
let idA = JSON.parse(ps.getProperty(drafts));
drafts.forEach(d => {
let idx = idA.indexOf(d.getId());
if(`idx)`){
d.send();
}
});
ps.setProperty('drafts','[]');
}

Note: I haven't debugged this last one at all.