允许通过电子邮件回复对Google Spreadsheets进行第三方更新？ [英] Allowing for third party updates to Google Spreadsheets via email response?

问题描述

我对Google Scripts来说相当新鲜，所以忍受我。我已经在Google Script中建立了一个用户界面，用户输入了所请求的日期，将信息存储在电子表格中，并向主管发送电子邮件。目前，我将邮件作为一系列字符串构建，并通过电子邮件发送给主管。没问题。

I'm fairly new to Google Scripts, so bear with me. I've built a user interface in Google Script that takes user inputs for requested days off, stores the info in the spreadsheet and sends an email to the supervisor. Currently, I am building the message as a series of strings, and emailing to the supervisors. No problem.

我被要求允许主管回覆请求（即批准或拒绝请求的链接或按钮）。应答应保存在与原始请求相同的电子表格中，并且在批准的情况下，还应访问第二个电子表格并更新员工累计工作时间。

I've been asked to allow for the supervisors to respond to the request (ie links or buttons that approve or deny the request). The response is supposed to be saved in the same spreadsheet as the original request, and in the case of an approval, should also access a second spreadsheet and update the employees accrued hours.

我的问题是，如何使用脚本完成这个任务？我需要通过电子邮件发送自定义的Google表单而不是当前的短信，还是有办法使用Google脚本？ （我以前没有使用Google表单，所以不知道它将如何表现，或者它是我当前项目的功能。）我一直在研究这个超过一个星期，并且空白。如果有人可以指示我一些文档或有任何见解，我会非常感激！

My question is, how to accomplish this using scripts? Do I need to email a customized Google Form rather than the current text message or is there a way to do this with Google Script? (I haven't used Google Forms before, so not sure how it will behave or it's capabilities for my current project.) I've been researching this for over a week, and coming up blank. If someone can direct me to some documentation or has any insight, I'd appreciate it greatly!

谢谢！

推荐答案

我被要求允许主管回覆请求（即批准或拒绝请求的链接或按钮）。

I've been asked to allow for the supervisors to respond to the request (ie links or buttons that approve or deny the request).

创建链接时，在此url中添加包含雇主ID，spreadsheetId和requestStatus等信息的其他参数。一个这样的网址可能如下所示：

When you create the link, add additional parameters onto this url that contain information such as the employerID, spreadsheetId's, and requestStatus. One such url may look like this:

https://script.google.com/macros/s/AKfycbwzwN5u3glvml0P1KoU5Q8snwwpvyGErikt_LPsvUn0hqznzC8/exec?id=201&ssId=1i6rFIliaIXjCWI7hkOkwlm_QBfY4ZiIf-Bbpscc6S2E&status=ACCEPTED

以下URL将导致您单独运行代码的webapp，如：

Following this url will lead to your separate webapp that is running code such as:

function doGet(request) {
  Logger.log(request.parameters.id);

  var sheet = SpreadsheetApp.openById(request.parameters.ssId).getActiveSheet();
  var data = sheet.getDataRange().getValues();

  var user = request.parameters.id;
  var status = request.parameters.status;
  for (var i=1; i<data.length; i++) {
    if (data[i][0] == user[0]){
      sheet.getRange(i+1, 3,1,1).setValue(status[0]);
    }
  }
  var result = "Congratulations! You just said '" + status[0] + "' to " + user + ".";
  return ContentService.createTextOutput(result);
}

这个 doGet（）函数读取URL的参数，然后使用其他参数数据从url参数 id 传递的ID更新电子表格。

This doGet() function reads the parameters of the URL, then updates the spreadsheet with id passed from the url param id with the other parameter data.

以下是我的设置链接。

这篇关于允许通过电子邮件回复对Google Spreadsheets进行第三方更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！