1

Problem:

I have a Google Form with duplicate question titles. I want to ingest the data from my spreadsheet into my own personal database. I need to build a mapping from Google Form Question to Google Sheet Column so that I can set the response value for the correct question in my personal database*.

As explained in this support thread, "Column mapping in the Form Responses tab is flexible and uses a behind the scenes reference rather than the question". How can I access this reference, or how can I determine which column a Google Form Question response goes to, or how can I determine which question a Google Sheet Column comes from?

I am using App Script, and after looking through the API, I still can't find what I am looking for. There already exists a method to get the Form related to a Google Sheet, so I would expect there to also be a method to return the mapping between a Google Form->Google Sheet as well.

How can I map between a Google Form Question and a Google Sheet Column?

*Technically, I can already do this but I wouldn't know the type of Google Form question, which I want to know. I want to know which choices were present on the Google Form when it was filled out by a respondee.

Example:

My Form:

My Question? (question-id: 1)
 + Answer 1
 + Answer 2
My Question? (question-id: 2)
 + Answer 3
 + Answer 4

In the sheet the responses get stored as

|      A       |     B        |
| My Question? | My Question? |
-------------------------------
| Answer 1     | Answer 3     |
| Answer 2     | Answer 3     |

If I change the question order on my form, it is now impossible for me to tell the questions apart using the Google Sheets header (the questions have the same name).

I want a one-to-one mapping like:

(question-id: 1) <--> Column A
(question-id: 2) <--> Column B

A -> My Question?

PAS
  • 149
  • 1
  • 8
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand ``map between a Google Form Question and a Google Sheet Column``. In order to correctly understand your question, can you provide the sample input and output situations you expect? – Tanaike Mar 01 '23 at 00:27
  • @Tanaike I have added an example of my problem, let me know if that helps your understanding. – PAS Mar 01 '23 at 00:35
  • Thank you for replying. I cannot understand `I want a one-to-one mapping like:`. What are `question-id: 1` and `question-id: 2`? – Tanaike Mar 01 '23 at 00:38
  • Hi @Tanaike, Each Google Form question has a unique question ID. See this link: https://developers.google.com/apps-script/reference/forms/item#getId() That is the ID I am referring to. – PAS Mar 01 '23 at 00:40
  • Thank you for replying. From your reply, I would like to confirm my understanding of your question. In your situation, how about directly retrieving all responses from Google Forms rather than the values retrieved from Spreadsheet? By this, even when the question items are moved, you can retrieve the answers for each correct question. Is my understanding correct? And, how about this approach? If I misunderstood your expected result, I apologize. – Tanaike Mar 01 '23 at 00:44
  • @Tanaike Ah you are right! https://developers.google.com/forms/api/reference/rest/v1/forms.responses/list with a timestamp > X is exactly what I am looking for. Thank you! – PAS Mar 01 '23 at 00:54
  • I still wonder if what I am asking is possible, but your suggestion should work. – PAS Mar 01 '23 at 00:54
  • Thank you for replying. From your reply, I proposed an answer. Please confirm it. If that was not useful, I apologize. – Tanaike Mar 01 '23 at 01:04

2 Answers2

1

I believe your goal is as follows.

  • From the discussion in the comment, I confirmed that your expected result is as follows.
  • You want to directly retrieve all responses from Google Forms rather than the values retrieved from Spreadsheet. By this, even when the question items are moved, the answers for each correct question can be obtained.

When this is reflected in a sample script, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet and set your Google Form ID and sheet name.

// Ref: https://tanaikech.github.io/2021/10/15/putting-all-response-values-from-google-form-to-google-spreadsheet-using-google-apps-script/
function myFunction() {
  const formId = "###"; // Please set the Google Form ID.
  const sheetName = "Sheet1"; // Please set the sheet name of sheet you want to put the values.

  // Retrieve all response values from Google Form.
  const form = FormApp.openById(formId);
  const headers = ["date", ...form.getItems().map(e => e.getTitle())];
  const values = [headers, ["itemId -->", ...form.getItems().map(e => e.getId())], Array(headers.length).fill(null), ...form.getResponses().map(f => {
    const timeStamp = f.getTimestamp();
    return f.getItemResponses().reduce((o, i) => {
      const r = i.getResponse();
      return Object.assign(o, {
        [i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r,
      });
    }, { date: timeStamp });
  }).map((o) => headers.map((t) => o[t] || ""))];

  // Put the values to the Spreadsheet.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(1, 1, values.length, values[0].length).setValues(values);
}

  • When this script is run, all response values are directly retrieved from Google Forms. And, put the values into the active Spreadsheet.

  • In this sample script, 1st row is the header row. And, 2nd row is the item IDs for the 1st header row. And, the response values are from row 4.

  • If you are not required to retrieve the item IDs, please modify them as follows.

    • From

        [headers, ["itemId -->", ...form.getItems().map(e => e.getId())], Array(headers.length).fill(null),

    • To

        [headers,

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
0

Each row of a grid question can be uniquely identified using its prefill ID.

PAS
  • 149
  • 1
  • 8