Mastering Google Sheets API

Need help APIs
21

Thank you. More questions…

How is the data getting from the sheet that the advisers are using into the CRM in the first place?

As I understand it, the only data you’re looking to get from the CRM are a list of added and removed opportunities. Is that correct?

What’s happening to this data after you get it? Is it being sent in a file to your client or should they be able to access the data through an interface in real-time? I ask because there’s a difference between pulling out data from Gsheets into Bubble and filtering data within Gsheets itself.

Whilst I’m here, the answer to both our questions appears to be explained in this Python thread (I don’t read Python but the concept may be the same for Bubble) :

https://www.py4u.net/discuss/610987

or at least, that’s as far as I’ve got.

22

No, Thank you!

So, data is travelling in the following order:

User Input → CRM → Internal WF exports data to Sheets → My App

My app simply uses the data deposited in Sheets to update itself. I’ve tried to get them to ONLY pass the newly changed data but the say it’s not technically possible due to their CRMs limitations.

In the end my app is a Business Intelligence platform where they visualize data.

I’ll take a look at the thread and let you know if there’s something I can take out of it :slight_smile: Thank you very much!

23

I think I get your problem, finally! It looks to me like you may want to do some pre-processing of the data before Bubble.

The way I see it, you have 2 lists, previous list (A) and current list (B).

List A holds records for all opportunities last week and list B holds records for all opportunities this week, some of which are new opportunities. List A also holds records of deleted opportunities, insofar as they do not appear on list B.

So a solution would be to merge the sheets, extracting the duplicates, leaving the new and deleted opportunities in separate sheets to work on from Bubble.

If you’re not already familiar with removing duplicates, you may find this helpful:

https://spreadsheetpoint.com/remove-duplicates-in-google-sheets/

Step 1 - Merge Old and New Sheets (Manually)

To create the deleted opportunities list, merge A and B, removing A’s duplicates from B to produce C. So that’s all previous opportunities, including deleted, minus all repeated (duplicate) and new opportunities.

To create the new opportunities list, merge A and B again, removing B’s duplicates from A to produce D. So that’s new opportunities, minus all repeated (duplicate) and deleted opportunities.

You may be able to do this through the sheets API. Requests  |  Sheets API  |  Google Developers

Good luck with that!

Step 2 - Bubble

In each case, you could query the remaining data from Bubble (using Sheets API perhaps) defining the range as the entire sheet, A1:Z1000 (as the API ignores empty spaces so the header should remain).

Your reduced list of opportunities may now be small enough to import into Bubble. If not, you can manipulate them with an API.

Does that help?

24

I made step 1 more complicated than necessary.

Make all previous list A’s values red and all current lists B’s values blue, merge and de-duplicate them (I think you can even de-duplicate across sheets, or at least tabs).

The remaining red values will be deleted opportunities, the remaining blue will be new opportunities. Sorry if that part is obvious!

25

Hi @dorianharris,

it seems that you had one year ago the same issue, as i have it now…
Could you perhaps give me a hint how you solved it?
I already posted this issue in the forum part for freelancers…

thanks a lot!!