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

Hi I started using TB last week and I really enjoy it.

I have a question regarding drop down menu.
Can I import a list from a google sheet to the drop down menu?

Thank you in advance!

1 Like

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.

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.

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.

2 Likes

Hi Scott,

Thank you for the quick and thorough answer! I could managed what I wanted!

1 Like