Can I extract a text from google sheet like a "Vlookup"?

Questions and Help
1

Hi, I would like to extract a certain text from a google sheet based on the selection of {formmenu} function, something like "vlookup" function in Excel. Is it possible?

For example, I would like to extract "country" and "programming" when you select "name" from {formmenu} in the example below.

[example]
{csv="name,country,programming\nShinta,UK,Python\nJames,Japan,Ruby\nAya,Italy,HTML"}
Hi I am {formmenu}. I live in {country}. My favourite programming language is {programming}.

I tried several method but could not find the way.

2

Yes, you can do that. You will need to convert your CSV to a list and then pull out the specific parts.

Here is an example:

{csv="name,country,programming\nShinta,UK,Python\nJames,Japan,Ruby\nAya,Italy,HTML"}

Convert the CSV to a list of lists. Also use slice() to remove the first header row:
{rows=slice(map(split(csv, "\n"), row -> split(row, ",")), 2)}

Hi I am {formmenu: values={=rows}; itemformatter=(row) -> row[1]; name=person}. I live in {=person[2]}. My favourite programming language is {=person[3]}.

3

Thank you Scott, I could manage this!

I also would like to apply the same logic to a google sheet which contains line breaks inside cells as shown in the picture below.

I searched several ways but could not find. If I publish as CSV, I guess CRLF and CR shall be detected and converted but not sure. Or Web, xlsx or other type are more suitable for this process..?

image
image

Example
{urlload: https://docs.google.com/spreadsheets/d/e/2PACX-1vQyoOJCNUPnTkxqLyWuMJzuGmFEkCj-G3sbxtHRMHEaEN5Gh3auF3qpol5GrSIaQxx1_oa73U3jAoos/pub?output=csv; done=(res) -> ["csv_data"=res]}{rows=map(split(catch(csv_data, ""), "\n"), row -> split(row,","))}

Hi I am {formmenu: values={=rows}; itemformatter=(row) -> row[1]; name=person}. I live in {=person[2]}. My favourite programming languages are as follows:
{=person[3]}

4

Your link doesn't load for me, so can't check your specific document. However, my guess is that Google Sheets is quoting cells that contain new lines. E.g. Python\nJavascript is being converted to "Python\nJavascript".

The simple CSV parsing format we used here, doesn't support quoting cells as it just breaks on newlines where ever they occur.

It's something you could add, but it would be fairly complex, it would be easiest if you just ensured the cells didn't contain any newlines (or, commas which would also lead to quoting).

5

Hi Scott,

Thank you for the comment!
I found a solution which replaces a keyword, ";" with "\n" after converting into row as follows!

image
{urlload: https://docs.google.com/spreadsheets/d/e/2PACX-1vQyoOJCNUPnTkxqLyWuMJzuGmFEkCj-G3sbxtHRMHEaEN5Gh3auF3qpol5GrSIaQxx1_oa73U3jAoos/pub?output=csv; done=(res) -> ["csv_data"=res]}{rows=map(map(split(catch(csv_data, ""), "\n"), row -> split(row,",")),cont -> replace(cont,";","\n"))}

Hi I am {formmenu: values={=rows}; itemformatter=(row) -> row[1]; name=person}. I live in {=person[2]}. My favourite programming languages are as follows:

{=person[3]}

1 Like