Can I use a list in google sheet for a drop down menu?

Questions and Help
2

Hi, and welcome to the forum! :slight_smile:

I assume you are asking about dynamically importing a list from a Google Sheet each time a snippet is used, so that the snippet will reflect the most recent contents of sheet.

If so this is possible, but requires a few steps.

Step 1. Publish the Sheet to Web as CSV

First you'll want to publish you Google Sheet. You can do so under the "Publish to web" option under the "File" menu. Make sure to select the "Comma-separated values" setting.

image
image936×1000 57.8 KB

Note that this published CSV will be public, so anyone can download it if they have the link. If you have G Suite, you can check the "Require viewers to sign in with their [YOUR COMPANY] account" option to lock access to your company.

Step 2. Load the Google sheet into Text Blaze

In step one you'll get a URL like:

https://docs.google.com/spreadsheets/d/e/2PACX-1vSHYc2fH_3MHQv5zyT3xKBCSxg1a4CU3rmwAN4rhY7AbX1rya-GNSxj8iLvb095WWbLOPpBiL9yAYYQ/pub?output=csv.

You can load that into Text Blaze using the {urlload} command.

{urlload: https://docs.google.com/spreadsheets/d/e/2PACX-1vSHYc2fH_3MHQv5zyT3xKBCSxg1a4CU3rmwAN4rhY7AbX1rya-GNSxj8iLvb095WWbLOPpBiL9yAYYQ/pub?output=csv ; done=(res) -> ["csv_data": res]}
{=csv_data}

Step 3. Create a menu from the CSV

If you copy the above snippet to Text Blaze, it will print out this data from the example CSV file:

Menu Value
Option 1
Option 2
Option 3

We need to convert that to the Text Blaze list of the options we want. In this case, we can do that by splitting by the new line character and skipping the first item:

We can use the values setting of the {formmenu} command to give it our list of options:

{csv_data="Menu Value
Option 1
Option 2
Option 3"}

{options=slice(split(csv_data, "\n"), 2)}
{formmenu: values={=options}}

Note that this logic will depend on how you structure your Google Sheet.

Putting it together

Putting it all together we have something like this:

{note}
{urlload: https://docs.google.com/spreadsheets/d/e/2PACX-1vSHYc2fH_3MHQv5zyT3xKBCSxg1a4CU3rmwAN4rhY7AbX1rya-GNSxj8iLvb095WWbLOPpBiL9yAYYQ/pub?output=csv ; done=(res) -> ["csv_data": res]}

{options=slice(split(catch(csv_data, ""), "\n"), 2)}
{endnote: trim=right}

{formmenu: values={=options}}

The example CSV doesn't require a log-in, so you can copy this snippet to Text Blaze and try it out.

4 Likes