Extract and display row from google sheet

I saw some examples in some posts for extracting data from a google doc. However I have a bit of a different task and am running into the bain of my existence, extractregex. I swear, one day I'll fully wrap my brain around it :grinning:.

So I have a google sheet with 7 columns. The goal is to display one row from the sheet. The row to be displayed can be identified with a string. This can be either input by using form or via the clipboard.

I'm hoping there is a way to match the string and display the full row that it is in within the snippet.

Is this a possibility?

So I don't know if I would necessarily use a regular expression for that. They should certainly work, but if you have structured tabular data, it might be easier to use other functions.

For example, say I had the some data in a CSV format and I wanted to show a specific row based on an ID column.

The split function allows me to break up my text into separate rows, and then break up a row into columns. The find function lets me get the first row that matches a condition (in this case having our target ID in the first column).

{target_id="XYZ2"}
{csv="ID,Color,Count\nABC1,Red,72\nXYZ2,Green,1\nQRT3,Purple,55"}
{=csv}

Target row:
{=find(split(csv,"\n"), row -> split(row, ",")[1] == target_id)}

Thanks so much!

I think I get it mostly, but I'm fuzzy on where the docs data is getting loaded.

I'm sure I'm skipping something simple. But here's what I have.

{urlload: https://thelinktomygooglesheet.com; done=res -> ["tokendata"=res]}

{target_id="{clipboard}"}

{=find(split(tokendata,"\n"), row -> split(row, ",")[1] == target_id)}

You could copy it to your clipboard. I believe copying a Google sheets will create a tab separated formatted piece of text that can be parsed similarly to above.

Directly urlload'ing a Google Spreadsheet is possible, but it may or may not work depending on how the spreadsheet is stored in the source of the HTML page. It may also be brittle and break if Google changes anything with how the Google Sheet's page is structured.

If you have a Google Spreadsheet, you can also make it available as a CSV file by using the spreadsheet's "Publish to Web" option.

You'll want to select the CSV export option and also "Require viewers to sign in with their [YOUR COMPANY] account". If the spreadsheet contains sensitive information that you would not want available to anyone your company with the link, you should not use this publishing approach.

This will then give you a URL for a CSV file that you can use in the urlload command to load the spreadsheet.

Note that there is may be some latency between when you make a change and when it is reflected in the published version. I believe this delay is about 5 minutes or less.

Aha, I think that is going to be my hangup on this one. Doesn't look like the spreadsheet data comes over in the source. Thanks for your help though, this actually clarifies quite a bit!

Actually I may have found a way, but the structure of how the data is returned is a bit of a mess. If I link the sheet to a doc, the source with data shows on urlload in the snippet. It's just all blocked together.

u001cTOKEN\n\u001c2020-08-10 20:58:15\n\u001cSECONDTOKEN\n\u001cISSUE\n\u001cINITIATED_BY\n\u001cSTATUS\n\u001cLOCATION

This is how each row shows up when coming from the google doc. I've bolded the data I'm trying to extract. The TOKEN is the identifier.

@Peter_Monterubio, the way I do it is by copying a row to my clipboard and then using the split command to break up the cells and assign them to variables:

Here's a snippet I made for copying spreadsheet contents and then extracting a specific cell:

{formtext: name=cell; default=a1; width=4}
{note: preview=no}
{columnName=extractregex(cell,"(\D+)")}
{row=extractregex(cell,"(\d+)")}

{columnList=["A"=1,"B"=2,"C"=3,"D"=4,"E"=5,"F"=6,"G"=7,"H"=8,"I"=9,"J"=10,"K"=11,"L"=12,"M"=13,"N"=14,"O"=15,"P"=16,"Q"=17,"R"=18,"S"=19,"T"=20,"U"=21,"V"=22,"W"=23,"X"=24,"Y"=25,"Z"=26]}

{col=catch(columnList[{=upper(columnName)}],"")}
{endnote}

{=upper(columnName)}{=row}: {=catch({=split({=split({clipboard},"\n")[{=row}]},"\t")[{=col}]},"Nothing found")}

And here's an example of how you can copy a whole row and assign the cells to variables:

{cell01=split({clipboard},"\t")[1]}{=cell01}
{cell02=split({clipboard},"\t")[2]}{=cell02}
{cell03=split({clipboard},"\t")[3]}{=cell03}
{cell04=split({clipboard},"\t")[4]}{=cell04}

If that's how the file is structured, you could split it on "\\n\\u001c" rather than "," like I have in my example above. We need to use "\\" rather than "\" as "\" is used to escape special characters in Text Blaze.

Again, I would be fairly cautious about directly loading the sheet due to the brittleness of the approach. I also believe Google Sheets only includes a portion of large spreadsheets in the initial load and then asynchronously loads the rest of the spreadsheet to decrease initial load times. When urlload'ing the spreadsheet directly, you only have access to the initial load.

Publishing the spreadsheet to CSV and then loading that would be a much more robust approach.

Hi there.
i can't get it to work.
How can I save 5 cells in a row (one range) from google sheets to clipboard and use them in a snippet to submit forms with auto pilot in 5 different text fields?
thank you

Ok, let me give you a more specific example.

Imagine you have a row with 5 cells (as you mentioned). Here's what that would look like:

If you were to copy those five cells into your clipboard, and then use the {clipboard} command in a snippet, it would give you this:

Although it looks like the cell contents are separated by a space, they are actually being separated by a tab.

I'm going to reproduce that same string of text below, and put it inside the variable named "content". Note that you won't need to do this in your snippet. This is just to illustrate the example.

{content="Column 1"&"\t"&"Column 2"&"\t"&"Column 3"&"\t"&"Column 4"&"\t"&"Column 5"} {note}- Here I'm creating the content, instead of using the clipboard command{endnote}

{=split(content, "\t")} {note}- Here I'm taking the contents of the variable called content, and splitting it up by tab spaces. This gives me the content in the form of a list of items.{endnote}

{=split(content, "\t")[1]} {note}- Once again, I'm splitting the contents by tab spaces, but by adding [1] after the split command, I'm telling Text Blaze to only give me the first item on the list. If I change that number, I'll get the corresponding item in the list.{endnote}

Now, here's what the snippet would look like in your dashboard:

{cell1=split({clipboard}, "\t")[1]}
{cell2=split({clipboard}, "\t")[2]}
{cell3=split({clipboard}, "\t")[3]}
{cell4=split({clipboard}, "\t")[4]}
{cell5=split({clipboard}, "\t")[5]}

These are the contents of cell 1: {=cell1}
These are the contents of cell 2: {=cell2}
These are the contents of cell 3: {=cell3}
These are the contents of cell 4: {=cell4}
These are the contents of cell 5: {=cell5}

Note that the forum doesn't allow the insertion of clipboard data, which is why the snippet above does not work on the forum. But try copying it to your Text Blaze dashboard and then copy a row from a spreadsheet and notice that happens.

Let me know if you get stuck anywhere :slight_smile:

Very helpful Cedric! works fine for me now! thank you!

1 Like

Cool.

If you're working with a column instead of a row, you can change the \t to \n for a new line.

Try it out and see how it works.

Hi @Cedric_Debono_Blaze

Since we have limit extracting details from Airtable, I am trying to find workaround using {clipboard} function.

After I copy row from airtable, it brings some data with One Cell showing value as Name1, Name2, Name3. I want to convert this to formmenu. I tried following but it is not converting it into dropdown.
List: {formmenu: values={=names}; name=personname} whereas names is above mentioned field.

Can you please help?

Hi @Pratik_Shah,

First you need to split the data by tab stops.

This should do the trick:

{fromclipboard="name 1 notes 1 Todo"}
{names=split(fromclipboard, "\t")}
{formmenu: values={=names}; name=personname}

The example I'm giving above uses a variable with predefined data for illustration purposes.

To use it with a row copied to your clipboard, change the first line to this:

{fromclipboard={clipboard}}

Let me know if you're still having difficulties :slight_smile:

1 Like

Thanks @Cedric_Debono_Blaze. That's working.

1 Like

You're welcome @Pratik_Shah :blush: