Allowing for third party updates to Google Spreads

问题:

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.

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!

Thanks!

回答1:

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

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

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

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.

Here's a link to my setup.