API Parse based on another cell

For the Crunchbase data, I’m trying to get the last funding stage of the company.
As the data pull all the funding stages, the way to do it would be to filter all funding stages based on “lastFundingOn” date to get the last funding stage.

=PARSE($D$7,"[‘organizations’][0].fundingRounds[?(@.[‘announcedOn’] == ‘2020-02-28’)]")
Does return the filtered funding stage list.

However when changing the “string” to a cell’s value:
=PARSE($D$7,"[‘organizations’][0].fundingRounds[?(@.[‘announcedOn’] == ‘$O$7’)]")
it returns an error. I’ve tried even putting text in a cell and returning just that in O7 cell, but it won’t work.

Does this mean API Parses don’t work with external cells within the doc? How else would you approach this?



Hey @vince :wave:t4: thanks for reaching out!

I suspect cell O7 contains the date 2020-02-28 and if so, can you please try this -

=PARSE($D$7,"[‘organizations’][0].fundingRounds[?(@.[‘announcedOn’] == ‘"&TO_TEXT($O$7)&"’)]")

Change recommended is from $O$7 to "&TO_TEXT($O$7)&" and here’s what it would finally look like (different cell references though) -

Hope this helps but do please feel free to write back in case you have any other questions around the same :blush:

1 Like

Awesome thanks a lot! The "& part was what made the TO_TEXT work! Which I didn’t have :slight_smile:

Thanks Sourabh

1 Like