Fill a table from a selected row

[PRS]

Hi

Wondering if this is possible:
• I have a table that I’m overwriting with data everytime that I need it to (specifically, it’s the funding rounds records from a GET_COMPANY_DETAIL_CRUNCHBASE call).
• As part of that output, each row has a cell with {data} of its own (specifically, the investors dataset for each funding round).
• I would love to be able to select a cell within that table and have a second table populated with that {data} (i.e., specifically, fill in a list of the investors in that selected funding round).
How can I make that second table get filled as a function of a selected record on the first table?

Thanks!

[sourabh]

Hey @PRS thanks for reaching out!

There are a bunch of ways to achieve this, depending on what you expect the final output to look like. Here are two variations of what I think might help.

For the purposes of this solution, I’ve -

• created 2 tables — T1: Funding rounds & T2: Investors
• used rows.com as an example input in cell A1 of the first table
• B1: =GET_COMPANY_DETAIL_CRUNCHBASE(A1)
• C1: =OVERWRITE(PARSE(B1,"['organizations'][0].['fundingRounds']"),A3:F3)

Method 1

• Add buttons in Column G
• Use the INSERT function in Column H in a way where each row is dependent on the corresponding row’s button click
  • H4 (as seen in the GIF below): =IF(OR($G$3="EXPORT ALL",G4="EXPORT"),INSERT(F4,'T2: Investors'!$A$1:$C$1),"Press 'EXPORT'")

The original {data} from the investors column (F) does not have the investmentType or the company name and so you only get the name of the investor, their domain and an indicator of whether or not they were the lead investor — which is what got me thinking that Method 2 may be better to consolidate these data points.

Method 2

• Insert a new column between investors and the export buttons called Modified JSON
• In the newly inserted column, modify the investor {data} to include the investmentType and the company details
  • G4 (as seen in the GIF below): =SUBSTITUTE(F4,'"name":','"company":"'&$A$1&'","investmentType":"'&A4&'","name":')
• Use the UPDATE function to export all the required data points to the investors table
  • I4: =IF(OR($H$3="EXPORT ALL",H4="EXPORT"),UPDATE(G4,'["company","investmentType","domain"]','T2: Investors'!$A$1:$E$1),"Press 'EXPORT'")

I’d be happy to connect and help you set this up on your spreadsheet as well

Please feel free to write back in case there’s anything more I could help you out with.

[PRS]

Hi Sourabh,

Thank you very much, Method 2 with the SUBSTITUTE function did the trick!!

One subsequent question, though: can I format the cells with the modified JSON so that they show as {data} rather than a long string (which is particularly cumbersome when I publish the app…)?

(btw, another un-related question: how can I format a cell containing a URL as a clickable hyperlink?)

All the best,
Pedro

[sourabh]

Glad this helped, Pedro

For both of these scenarios, it so happens that we default to a string output for most of these functions (SUBSTITUTE, CONCAT etc.).
This has come-up before too but I’ll ensure to reach out to our Product team and share this as a feedback / feature enhancement request.

As for a solution, here’s what I recommend -

1. Nest the SUBSTITUTE function inside the UPDATE function and that would allow you to eliminate the Modified JSON column altogether.
   • H4 (as per the screenshot below): =IF(OR($G$3="EXPORT ALL",G4="EXPORT"),UPDATE(SUBSTITUTE(F4,'"name":','"company":"'&$A$1&'","investmentType":"'&A4&'","name":'),'["company","investmentType","domain"]','T2: Investors'!$A$1:$E$1),"Press 'EXPORT'")

2. To make the URLs in Column E from the Investors table clickable, I propose modifying the original investors json and appending http:// to the domain values in them.
   • H4 (encapsulating the original SUBSTITUTE function with another SUBSTITUTE): =IF(OR($G$3="EXPORT ALL",G4="EXPORT"),UPDATE(SUBSTITUTE(SUBSTITUTE(F4,'"name":','"company":"'&$A$1&'","investmentType":"'&A4&'","name":'),'"domain":"','"domain":"http://'),'["company","investmentType","domain"]','T2: Investors'!$A$1:$E$1),"Press 'EXPORT'")

I suppose there could be a way to use a single SUBSTITUTE too but I simply chose this approach out of my comfort
Please feel free to modulate it as per your needs as well.

Cheers!

[PRS]

Got it! Embarrassingly simple! Thank you, Sourabh!

More cell formatting options would be welcome indeed - thanks for taking it up with the Product team!

(I couldn’t get the “http://” workaround to work outside of a filled table, on an individual cell simply using PARSE( )… But that’s allright, we’ll leave it to a different forum post)

Thanks again!
Cheers!

[sourabh]

Ah! It’s all good. I’ve had some learning on this myself. Thanks to one of our product people, they pointed out that we could also make use of the PARSE function and get the {data} format inside the cell - I totally missed that

So, we could’ve also done this in cell G4 (in the Modified JSON column) from Method 2 -
=PARSE(SUBSTITUTE(F4,'"name":','"company":"'&$A$1&'","investmentType":"'&A4&'","name":'),"$")

I hear you on the http:// part too and here’s another workaround to make it independent of filling a table (a bit tedious though) -

• let’s take T2: Investors table
• create a new column — column F, called formattedDomain — next to the domain (column E) and use the CONCAT function to add http:// on each row
• finally, use the INSERT function in cell G1 to get the clickable URL data
  • G1: =INSERT(RANGE2JSON(F1,F2:F))

We can’t hide columns (yet ) but this was the next best thing that I could think of.

Caveat: This logic would break if there are any empty cells in Column F because the RANGE2JSON function would simply skip those positions.

Please keep these questions, scenarios and challenges coming