Get a link to an individual Google Forms response from a Google Sheet

Question

I have a Google Form which outputs responses to a Google Sheet. Some of my form questions have long paragraph responses, so I'd like to be able to click a link on each row from my tracking sheet and have it open the original response page as seen from the Form's "responses" tab.

The URL I'm trying to generate, which I get by going to Responses -> Individual from my Form, looks like:

https://docs.google.com/forms/d/<form_id>/edit#response=<response_id>

I have looked at the FormResponse API documentation, but I can't manage to extract the same ID used by the Google Form viewer from a FormResponse object. While I know the appropriate form ID, the response ID that is valid for that URL isn't the same one returned by either FormResponse.getId() or as part of FormResponse.getEditResponseUrl(). In other words, I can't get the data from the Forms API that's needed to generate that link. I'd rather not use edit links, which they do support generating, to view my responses.

My end goal is to have a link with the format shown above, in each row of my response form. A correct link with that format brings you to the individual response viewer page for the particular response in that row. i.e., I'd like to turn this:

+-----------+------------+
|Question 1 | Question 2 |
+-----------+------------+
|Long answer| Another ans|
+-----------+------------+
|One more an| Additional |
+-----------+------------+

Into this

+-----------+------------+------------------------------------------------------------+
|Question 1 | Question 2 | View link                                                  |
+-----------+------------+------------------------------------------------------------+
|Long answer| Another ans| https://docs.google.com/forms/d/abc123/edit#response=def456|
+-----------+------------+------------------------------------------------------------+
|One more an| Additional | https://docs.google.com/forms/d/abc123/edit#response=ghi789|
+-----------+------------+------------------------------------------------------------+

For reference, I tried generating the URL as "https://docs.google.com/forms/d/" + formId + "/edit#response=" + responses[i].getId(), but when visiting that URL it says the response ID is invalid.

Is there a way, either via an Apps Script or other configuration, to produce a link back to each individual response in my output sheet?

"I'd rather not use edit links to view my responses." That presumably rules out Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script. But it's not clear to me just what you want instead. Would you please elaborate. — Tedinoz
@Tedinoz I want to have a hyperlink from my Sheet which brings you to the view-only page for that specific response. When viewing a Form you can click "Responses -> Individual" and it shows you a rendered view of the form with a single response's content in it. You can flip through the responses/choose a particular one and it shows you that one. Each response has a unique URL, so I'm hoping there is a way to recover that URL and use it as a link. — Wasabi Fan
@Tanaike I still believe it's the best alternative so far. To be clear, Whatever I commented were the OP's views and not my view. — TheMaster
@TheMaster Thank you for your support. I would like to modify the proposal for approaching OP's goal. When I did this, I would like to propose it. I appreciate for your always support. — Tanaike
@Tanaike I still don't fully understand which part you'd like clarification on, but I've done my best to improve the question. Let me know if it still isn't clear. — Wasabi Fan

Tanaike Tanaike · Accepted Answer · 2019-09-01T06:45:48

You want to achieve to create the Spreadsheet which has the following value using Google Apps Script.

+-----------+------------+------------------------------------------------------------+
|Question 1 | Question 2 | View link                                                  |
+-----------+------------+------------------------------------------------------------+
|Long answer| Another ans| https://docs.google.com/forms/d/abc123/edit#response=def456|
+-----------+------------+------------------------------------------------------------+
|One more an| Additional | https://docs.google.com/forms/d/abc123/edit#response=ghi789|
+-----------+------------+------------------------------------------------------------+

You want to see the response page with the read only.

If my understanding is correct, how about the following workaround?

Issue and workaround:

The endpoint of each response cannot be directly created.
The URL retrieved toPrefilledUrl() can submit. So it is not read only page.

Unfortunately, from above situations, it is required to think of the workaround. In this workaround, I used toPrefilledUrl() and Web Apps. The flow of this workaround is as follows.

Flow:

Create Spreadsheet by putting above ascii table.
- Web Apps is used as the URL of "View link". And the URL retrieved from toPrefilledUrl() is used as the query parameter.
- Web Apps is deployed before.
When users access to the link, Web Apps is opened.
- When Web Apps is run, HTML data is retrieved from the URL of toPrefilledUrl(), and the submit button is removed. Then, the edited HTML is opened.

By this, the read-only response can be displayed.

Usage:

In order to use this workaround, please do the following flow.

1. Copy and paste script

Please open the script editor of the Google Form you want to use. And copy and paste the following script to the script editor.

function doGet(e) {
  var url = Utilities.newBlob(Utilities.base64Decode(e.parameter.u)).getDataAsString();
  var html = UrlFetchApp.fetch(url)
    .getBlob()
    .getDataAsString()
    .replace('<span class="quantumWizButtonPaperbuttonLabel exportLabel">Submit</span>', "");
  return HtmlService.createHtmlOutput(html);
}

function myFunction() {
  var webApps = ScriptApp.getService().getUrl();
  var form = FormApp.getActiveForm();
  var items = form.getItems();
  var headers = form.getItems().map(function(e) {return e.getTitle()});
  headers.push("View link");
  var formId = form.getId();
  var responses = form.getResponses();
  var obj = responses.map(function(e, i) {
    var temp = e.getItemResponses().reduce(function(o, f) {
      o[f.getItem().getTitle()] = f.getResponse();
      return o;
    }, {})
    temp["View link"] = "=HYPERLINK(\"" + webApps + "?u=" + Utilities.base64Encode(e.toPrefilledUrl()) + "\", \"response" + (i + 1) + "\")";
    return temp;
  });
  var values = obj.map(function(e) {
    return headers.map(function(f) {
      return f in e ? (typeof e[f] == "object" ? e[f].join(",") : e[f]) : "";
    })
  });
  values.unshift(headers);

  // As a sample, it creates new Spreadsheet and put values.
  var sheet = SpreadsheetApp.create("sampleSpreadsheet").getSheets()[0];
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

2. Deploy Web Apps

On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
Select "User accessing the web app" or "Me" for "Execute the app as:".
Select "Anyone, even anonymous" for "Who has access to the app:". This is a test case.
- If Only myself is used, only you can access to Web Apps. At that time, please use your access token.
Click "Deploy" button as new "Project version".
Automatically open a dialog box of "Authorization required".
1. Click "Review Permissions".
2. Select own account.
3. Click "Advanced" at "This app isn't verified".
4. Click "Go to ### project name ###(unsafe)"
5. Click "Allow" button.
Click "OK".

3. Run script

Run myFunction(). By this, new Spreadsheet is created.
Open the created Spreadsheet.
Click the cell of "View link".

By this, Web Apps is run and you can see the response page with the read only.

Note:

When you modified the script of Web Apps, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. When the Web Apps is not redeployed even when the script is modified, the latest script is not used. Please be careful this.