How to create dynamic list items in Google forms from 2 arrays

Question

I have this in a spreadsheet:

REGION	SITE
R1	S1
R1	S2
R2	S1
R2	S2
R2	S3
R3	S1
R3	S2
R3	S3

In a form, the user can pick a region and I want to generate a list item with only the sites related to the chosen region.

I tried with a map/object based on this post

but the result only keeps the first site for each region

{
 R1 : S1,
 R2 : S1,
 R3 : S1
}

What I want (at least I think it could help me to get a depending list validation):

{
 R1: [S1,S2],
 R2: [S1,S2,S3],
 R3: [S1,S2,S3],
}//",}" added by editor

I have to apologize for my poor English skill. Unfortunately, I cannot understand your expected value. Can you provide your expected value? — Tanaike, Oct 18 '22 at 08:54
Dynamic options are not supported by Google Form. You should consider to create your own Form with [HTML Service](https://developers.google.com/apps-script/guides/html) — idfurw, Oct 18 '22 at 09:33

score 1 · Accepted Answer · answered Oct 18 '22 at 11:59

From your provided sample expected value, I believed your goal is as follows.

From your showing table (Spreadsheet), you want to retrieve the following value using Google Apps Script.
```
  {
    "R1":["S1","S2"],
    "R2":["S1","S2","S3"],
    "R3":["S1","S2","S3"]
  }
```

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  const values = sheet.getRange("A2:B" + sheet.getLastRow()).getValues();
  const res = values.reduce((o, [a, b]) => (o[a] = o[a] ? [...o[a], b] : [b], o), {});
  console.log(res)
}

When this script is run, res is {"R1":["S1","S2"],"R2":["S1","S2","S3"],"R3":["S1","S2","S3"]}.

Testing:

From your showing table, values is [["R1","S1"],["R1","S2"],["R2","S1"],["R2","S2"],["R2","S3"],["R3","S1"],["R3","S2"],["R3","S3"]]. When this value is used, the test script is as follows.

const values = [["R1","S1"],["R1","S2"],["R2","S1"],["R2","S2"],["R2","S3"],["R3","S1"],["R3","S2"],["R3","S3"]]
const res = values.reduce((o, [a, b]) => (o[a] = o[a] ? [...o[a], b] : [b], o), {});
console.log(res)

Note:

In your expected value, you show as follows. But, I thought that from your showing table, R2 of 2nd R2: [S1,S2,S3] might be R3.
```
  {
   R1: [S1,S2],
   R2: [S1,S2,S3],
   R2: [S1,S2,S3]
```

Reference:

reduce()

@mthgn Thank you for replying. I'm glad your issue was resolved. Thank you, too. — Tanaike, Oct 25 '22 at 23:25

How to create dynamic list items in Google forms from 2 arrays

1 Answers1

Sample script:

Testing:

Note:

Reference: