2

I am looking for a way to send an email with Google App Script (I know how to do that) which includes an approval button/link. When the user clicks the link/button in the email to approve the request, I want to trigger a script (this is the part I do not know if possible from email) which goes back to the Google Sheet and changes a value in a cell.

Example: User A submits a request using google form, and a new row is appended in SheetA. With some formulas, I automatically associate an ID = X to that request/row. A script will then send an email to user B to ask to approve the request of user A. User B will click a link in the email and the script will find the record X in SheetA and update a value in that row.

Filippo
  • 320
  • 2
  • 5
  • 22
  • I think this is a good question, would you mind cleaning it up a little? I think it could be much shorter and concise. – Stykes May 20 '20 at 23:41

2 Answers2

4

I don't know of a prefect way to do this, but with a small work-around, it is possible.

Instead of "clicking an Approve button," the user could click an Approve link. This link would open a Google Form that is pre-filled with the a request ID and Marked as Approved. All they would need to do is submit the pre-filled form. The email would also contain a Reject link that would also pre-fill the request_id and mark Reject. (I recommend radio buttons for this question)

Then set up a trigger onFormSumit(). for this form that makes the appropriate edits to the spreadsheet when a user submits this form.

If you wanted to make the solution even more complicated, but perhaps avoid the second click, the links could be to a WebApp and you could code the site to automatically record the data in the url to Google sheet.

PS: Both solutions also work as a way to get read receipts for emails sent from Google Scripts.

Stykes
  • 306
  • 6
  • 15
  • I am going to hold for another day, but I thought about something similar to your second solution and I will probably go for that. Thanks! – Filippo May 20 '20 at 23:43
4

You can create a simple web app, using code like

function doGet(e) {
  params = e.parameter;

  var range=e.parameter.range;
  var value=e.parameter.value;

  SpreadsheetApp.openById('SHEETID').getRange(range).setValue(value);

  return HtmlService.createHtmlOutput(`<b>${range}</b> set to <b>${value}</b>`);
}

publish it with "execute as me", "everyone, even anonymous" options, and then exec it using a link

https://script.google.com/macros/s/SCRIPTID/exec?range=B1&value=100

Example sheet: https://docs.google.com/spreadsheets/d/1jNEzil1dOtXj-Qc5ZVcZnd1fwGQ7V18irWIsH9G8to8/edit?usp=sharing

Update link: https://script.google.com/macros/s/AKfycby5KYzQELQNS0y0uf0CwjwzoWkabCJywqkpph7wUUXoTGjERUg/exec?range=Sheet1!B1&value=500

roma
  • 1,512
  • 10
  • 20