Updating a specific cell in a Google sheet using a hyperlink

Question

I have a Google sheet with some logic (script) behind it that sends an email with the details of the last row add to it (using the attached Form).

Once the recipient gets the email, he needs to confirm it. I would like to add an additional hyperlink to the mail, that will update a specific column in that row.

I know I can use the "Get link to this cell" to get the URL containing the spreadsheet key and sheet number along with the cell range, but this is helpful only when one wants to open the sheet and set the current cell. This is not sufficient enough, since:

1. I need to update the cell value with a fixed string ("Confirmed")
2. Access to the sheet is very limited, so I would like to allow updating of just this specific cell using the hyperlink, without having to open the sheet.

Is that possible? If so - how?

Thanks, Eran

Answer 1

solution #1

1- You can setup a filter on gmail to group the responses with a specific label

2- Then you can trigger every day or every x minutes a function that will write in your spreadsheet, for instance

function receiveEmailsWithLabel() {
  var libelle = GmailApp.getUserLabelByName('the label you have definied');
  var conversation = libelle.getThreads();
  for(i in conversation){
    if(conversation[i].isUnread()){
      var message = conversation[i].getMessages()[Number(conversation[i].getMessageCount()-1)];
      var text =  message.getPlainBody(); 
      var sh = SpreadsheetApp.openById('the id of the spreadsheet').getSheetByName('the name of the tab')
      // do someting in your spreadsheet, for instance
      sh.appendRow([text])
      conversation[i].markRead()
    }
  } 
}

solution #2

send a link to a form with the email and ask participants to respond via the form (this is the easiest way)

Answer 2

• Unfortunately there isn't a direct built-in link to do that.
• However a simple WebApp, if properly deployed, can achieve this task easily.

Here is a implementation suggestion:

1. On the target worksheet, have a column to store a unique identifier for that recipient. (i.e. a UUID field)
2. When generating the confirmation URL to send via email, make sure to add that unique identifier as a URL parameter.
3. Create a standalone Apps Scripts project and make sure the creator has Edit access to the target Sheet file.
4. When deploying the WebApp, make sure to choose Execute as as me (project creator with Edit access to the Sheet file) and Who has access according to your recipient user target (either from your Workspace org or users with Google accounts or even accepting any user).
5. Make sure to run at least once your code from Apps Scripts editor to authorize it.

• The usage (in this case, the URL to be sent via email) will look something like this: https://script.google.com/macros/s/<DEPLOYED_SCRIPT_ID>/exec?id=<UNIQUE_ID>, where DEPLOYED_SCRIPT_ID can be retrieved once you deploy it, and UNIQUE_ID is the identifier on the target Sheet.
• Here is a sample implementation of this WebApp:

Sample Code (WebApp):

const SHEET_ID = "<SHEET_FILE_ID>"; //Drive ID of the confirmation Sheet file
const WORKSHEET_TAB_NAME = "<WORKSHEET_TAB_NAME>"; //Name (case sensitive) of the worksheet tab where the cell of confirmation flag lives.

function doGet(e){
  var outputHtml = "";

  if (e.parameters.id){ //GET parameters contains id?
    var ss = SpreadsheetApp.openById(SHEET_ID); // Get Sheets file
    var ws = ss.getSheetByName(WORKSHEET_TAB_NAME); // Get worksheet tab to be modified by name.

    var textFinder = ws.createTextFinder(e.parameters.id[0]); //initialize a TextFinder object to find the row with provided ID
    textFinder.findNext(); //find next Occurrence of the ID provided

    var idMatch = textFinder.getCurrentMatch(); //get Range for the match

    if (idMatch) { //if idMatch is not null (if it returned results)
      //Adjust the cell offset below to match the "Confirmation column"
      var confirmedCellRange = idMatch.offset(0,1); //this offset means one column to the right of the found ID on the same row

      if (confirmedCellRange.getValue() === ""){ //if empty, "Confirmed", otherwise ignore
        confirmedCellRange.setValue("Confirmed"); //set cell value as "Confirmed"
      }
    }

    outputHtml = outputHtml + "<p>Done!</p><br>";
  }
  return HtmlService.createHtmlOutput(outputHtml);
}