google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-formsgoogle-forms-api

Determine Google Form question reference to Google Sheet column

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?

Solution

  • I believe your goal is as follows.

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

    Reference: