Google Sheets Integration

There is no native support for GSheet integration/sync into Fibery.
It’s possible that other community members have experience with developing a custom integration, but I’ve not heard much.

Otherwise, your only options are CSV import (or using report view to create analytics if that’s all you need).

2 Likes

Hi @oleg and @Chr1sG,

Is there by now a way to pull Google Sheets data into a database without a custom integration? I know for reports I can pull the data, but I would like to add them to a database.

Is that possible yet?
Thanks!

1 Like

You don’t need a Custom Integration, but you do need to do it manually with some Javascript.

  • Create an “export” link for your Google sheet (File > Share > Publish to Web > Link). You can export a single tab or all tabs in the sheet.

  • In your Fibery script use http.getAsync() to read your exported sheet and process it accordingly.

I believe the only way this will work is if your exported Sheets link is publicly accessible, but only someone who has the URL will be able to access it.

4 Likes

Thanks @Matt_Blais, for the tip! That’s great to hear!

Sadly, it involves my old nemesis and abomination of programming language[1]: JavaScript. :face_with_peeking_eye:

let url = <published google sheet url>
http.getAsync(url)
    .then(response => response.json())
    .then(myJSON => fibery.createEntityBatch("Members", myJSON));

The above is how I think it should work, but it’ll surely not work because:

  1. The whole promise and await syntax and .then( => ) is just confusing
  2. fibery.createEntityBatch surely doesn’t just take that JSON, but there is no documentation

I’d be much obliged for any tips on what that JS code should actually be?

Lastly, @Chr1sG, having to publish the spreadsheet is not good. In this case, it’s a member database, so Ideally I wouldn’t want to have it publicly available even through an obscure URL. Instead, I rather give Fibery access to my Google Drive, like when making a report.

Thanks again!

[1] Horrible dev stack and debugging + weak typing + horrible documentation + random (browser) side-effects => :face_with_symbols_over_mouth: mess :face_with_symbols_over_mouth:

Welcome to Fibery scripting hell :stuck_out_tongue_winking_eye:

You can use the javascript await syntax - much simpler than using Promises with .then()

    const result = await http.getAsync(...)

You can also access sheets via Google’s REST API, which will give you more auth options (and way more complexity to deal with).

Wheee, scripting hell is probably what I do deserve. :see_no_evil:

Don’t get me wrong, @Matt_Blais, I very much appreciate your help.
But I reaaaaaaaaaaally wish Fibery would solve this natively. Especially as reports can import Google Sheets. :melting_face:

2 Likes

Agreed! Although the native Fibery forms now largely replace the usefulness of Google Forms (as per the original request here), I still think there is a significant benefit to connecting with Google Sheets as a data source for migration to a more full database setup in Fibery. This is particularly important as Fibery lacks any good support for spreadsheet-like functionality in non-database tables, and very, very often I find that a full database is far too “heavy” of a solution for some data I want to quickly summarize, run quick calculations on, etc. That kind of work is still perfect for Google Sheets, but then some of it eventually turns into something I do want to do more extensive database operations on, and then moving it into Fibery would be ideal. I talk about this broader concept in a more forward-looking way here and outline some of the needs and benefits:

And all of that of course doesn’t even account for the many, many other tools already integrated with Google Sheets, for which GSheets could then become an intermediary for getting data to Fibery. It seems like on that basis alone it’s a high value thing to implement. As you say Reports already connect, and since it’s all just basic tabular data it seems like it should be fairly trivial to connect with. Why not Fibery team? Why not. :thinking:

1 Like

Although Google Sheets is ‘just tabular data’ there is quite a lot of complexity to consider, for example:

  • assuming that each column in a sheet should be matched to a field in Fibery, how can Fibery know what datatype each column of data represents (is it a date, a number (integer/decimal), select field…)?
  • following on from that, how could Fibery adapt if the content of a cell changed such that it was no longer a supported value for that data type? In other words, if the sheet has a column of numbers, Fibery might ‘intelligently’ sync these as a number field, but then what should happen if one of the cells is edited to become a string of letters?
  • what should happen if the user adds more columns? should Fibery automatically add new fields?
  • what should happen if columns are deleted or reordered in the sheet? How does Fibery recognise this (as opposed to perceiving it as changes to the field types and values)?
  • what should happen if rows are deleted/reordered in the sheet? Is it to be expected that Fibery somehow intelligently compares before and after, in order to recognise that the data that was in row 1 (which was entity 1) is now in row 10 (but should still be identifiable as entity 1).
  • sheets are (almost) unlimited in the number of possible columns. Fibery databases are limited in the number of possible fields. Should the sheet data be truncated?

My guess is that different people might have different answers to these questions.
For example, some people might be happy for everything to simply be synced into Fibery as a text field (which is the most flexible/accommodating datatype) whereas others might require numbers to be synced as numbers.
Some might even just want to have a Google sheet embedded into a Fibery document, which is already possible :slight_smile:

Overall, although the use case “Sync Google sheets into Fibery” sounds like something a lot of users might have, in practical terms, it might actually be dozens of slightly different use cases, which typically makes for v difficult implementation.

4 Likes

Fair points and well articulated! As with many things of this type my approach would generally be to not bother trying to answer all those questions for everyone, but simply to answer them internally for whatever has the most consensus then do an MVP (Minimum Viable “Product”, not Most Valuable Player, of course :smile:). Similar to what was done with the AI stuff in last December’s “slow time”, which of course turned into much more because it proved its value. (in fact perhaps this could be someone’s “slow December” project, if they feel so inclined…)

The truth is that with many things we can (and often do!) speculate a lot about what people might want or what differences of opinion there might be, but the best way to find out is to release it to users and see how much of an uproar you get. :laughing: This is not a good approach for all companies, but for Fibery IMHO it really, really is for the following surely compelling reasons :smile: :

  • Fibery dev and release already works this way. That’s really all I should need to say, but I’ll say more. :wink:
  • Fibery has many things that are implemented only “partially” (depending on use case/user perspective/needs), or work only partly well, or are far less sophisticated than many users would like them to be (e.g. very limited bi-directional integration support, the until-recently very limited Tables View, Whiteboard, etc, etc.)
    • Many of these things are “good enough” to continue existing despite their limitations and still contribute significant value to the platform
  • There is already an existing mechanism for experiments, some of which do not become permanent
  • Using this mechanism (most of the time), Fibery has already released things, some of them significant (e.g. nav experiments), that it later removed/changed
  • Furthermore the existing user base is comparatively small, so even if some experiment has negative results it affects a small number of people
  • Fibery has not found true market fit, adoption is still slow and unclear (according to latest monthly progress update), and this has been the case for years now, so more changes probably need to be added/tested

Google Sheets embed is complimentary to a Fibery sync IMO, especially if the sync is bi-directional. I would love to be able to sync some Fibery data out to a GSheet and run some quick, ad-hoc calculations on it! I don’t expect per-entity calculation fields or Ad hoc formulas in views to be added soon, or even this request that Michael said would be considered:

So short of those, this would add most of the benefits of that in some sense, and complement existing functions like embed, reports, etc. that already work with GSheets…

If you need suggestions for specific answers to any of the above questions and more I’m happy to think through the practicals of an MVP!

1 Like

At the moment, I have yet to actually read a single well-defined use case* so for any MVP it feels like we’re a long way from ‘consensus’ :stuck_out_tongue:

*apart from form submission and embedding, which are both solved :crossed_fingers:

But actually, I feel like it’s a square peg-round hole thing. If someone said they wanted to sync Word with PowerPoint I’d be similarly confused about what that could mean.

I should note that your example is now the first:

which I personally believe should be solved in some other way than a bidirectional sync with GSheets

2 Likes

I can work on use cases if the potential implementation will actually be taken seriously as a possibility for some relatively near future. Otherwise, not to be dickish, but I don’t think I want to invest the time to specify and articulate them for likely little tangible benefit to anyone. :sweat_smile: @Matt_Blais did you have any other specific use cases top of mind besides the now-solved form input one?

The bottom line as I see it is that GSheets is a useful way to have more “loose” data, ad-hoc calculations, etc., but it is almost inevitable (in my experience) that at least some of that data starts to become more “firm”, and eventually often needs a more formal solution (e.g. Fibery). I suppose at that point I can just do a one-time CSV import, but then I lose the benefit of GSheets and it makes it much more consequential and thus unfortunately disinclines me from using Fibery more (i.e. I have to be more certain that I need the Fibery formality and “heaviness” and willing to let go of the flexibility, which makes the bar higher). The easier you make it to work between Fibery and other tools, the more toeholds I think you’ll get in multiple markets.

Cool, great, I agree!.. How?

And let me add to that: even if we can both agree there is a more optimal way to solve it, again I am talking here about what I still think is relatively “low hanging fruit” for a GSheets integration. And I strongly suspect that any more optimal solution is going to take a good deal more time and effort to provide vs. this. But I could be dramatically incorrect. Perhaps you have good suggestions for how to do what I want to do with already existing functions, or perhaps I am wrong and GSheets integration - even in MVP form - is very hard, or perhaps… something else. :smile:

I am not a dev in our integrations team, so I can’t give a qualified technical answer, but I suspect the problem is not that it is extremely technically difficult to implement something, it’s just very difficult to define what that something should be, and no developer wants to work on a vague ‘sync Fibery to GSheets’ specification.

Well, for the time being, I would suggest trying to achieve what is needed with either Ply, Make or Zapier.

And if a handful of users do this, and discuss here what they did, it would go some way to illuminating what an MVP might look like.
My prediction is that there will be very little overlap between different users’ setups.

I understand that, and it is quite possible that GSheets integration will never be implemented, so I don’t blame you.
But then again, if there aren’t users coming forward to articulate a clear need that they personally have, then one could argue that it isn’t a massive problem to be solved.

Finally, is anyone here in the community aware of any tool comparable to Fibery (notion, coda, airtable) which has native GSheets integration?
(not via scripting / a 3rd party tool and more than just embedding a sheet)

It has been a while since I thought about this. The main driver for me is that one of my clients keeps A LOT of their business data in Google Sheets, and still actively manages it there; much of it would be quite useful to have mirrored in Fibery, if it doesn’t end up being migrated to Fibery entirely.

One-way sync (Sheets → Fibery) would be fine for much of it (and that would cover migration), but there will probably always be cases where the data has to continue to live in Sheets/ClickUp (due to policies/politics/etc), so it could also be useful to have two-way sync.

Fibery’s Custom Integration capability could do most of this, but those are very complex to set up.

Another approach would be to write a Sheets Add-On (Google Apps Script) that would push changes to Fibery (based on criteria defined within Sheets), and perhaps a Fibery Rule to push some info from Fibery to Sheets.

1 Like

Have you tried playing around with Ply? I feel like it might help with both of these.

Regarding https://ply.io/ – it’s an awesome idea, but (1) still quite immature, and (2) looks to be prohibitively expensive, by my standards:

For internal features (features you share with your team), it’s likely going to be seat based. There’s likely going to be a free plan with limited usage, a “plus” plan for small teams in the $10 to $12 per seat/month range that will be capped somehow (like number of feature runs or tasks used) and a plan with nearly unlimited usage for ~twice that price for heavy users, which will also include features for larger teams. There will also be an Enterprise plan with advanced security features that will be custom pricing.

As for customer-facing features (which are still in a closed beta), that’s likely to be usage based. No idea about pricing here yet. We need to see some usage patterns and the value our customers get to price accordingly.

Again, this is a general direction and not a commitment, but I understand why this is important and we’re trying to be as transparent as possible about this. Hope this helps :slightly_smiling_face:

How much is a GSheets integration worth to you then?

I actually don’t have a pressing need for it right now, so not a lot… but when the need does show up it will need to be done yesterday :joy:

1 Like

To start, I do not need two-way sync, but the ability to pull data from Google Sheets to Fibery.
I think that’s in line with Fibery’s philosophy to be the fabric connecting data. Sadly we have to collaborate with others via Google Sheets, so being able to pull that info into Fibery is very helpful.

For me, MVP would be to be able to:

  1. Import Google Sheet data into a database based on a fixed mapping that I configure
  2. Regularly add entries that do not exist (based on the config mapping hash) to Fibery as long as the mapping works
  3. An upsert would of course be preferred, if easily possible

That way I can encourage the team and partners to gravitate to Fibery and enhance the entities in Fibery, connect them, etc.
And yes, the Fibery Forms are great help and very useful even for processes, but pulling data in form other sources is what powers the engine. And some of that stuff comes in through Google Sheets. :frowning:

Thanks!

1 Like

@Oshyan, interesting thoughts.

As somebody who’s a huge fan of “simple tables” and databases, it makes me think that it’s actually more of an interface problem:

  • I love Flat Tables (I think is a better name than “simple tables”) as part of text and notes. They allow to nicely structure data along a few dimensions, as OL/ULs do not.
    • Thus I’m also a big fan of merging cells, coloured backgrounds, headers, footers, etc.
    • Flat Tables often will have more rich text than more spreadsheet views: summaries, some breakdown with some OLs, etc.
    • These tables are predictable or “frozen” and often hold high-level information to drill deeper
  • I also love Deep Tables (embedded table views of databases in Fibery lingo) as they allow to apply dynamic filters, sorting and all other fancy stuff
    • Most importantly, the strength off these are that data is connected and remains up-to-date
    • These can allow for calculations (sums, averages) and other computation on top of it.
  • I agree that ideally these should be on a spectrum so that the same data should be possible to display as a Flat Table or a Deep Table. What I currently often do, are simple tables with embedded Entities, which obviously does not do the necessary updating if needed.
  • If there would be a way to embed a View and then select if this should be a deep or flat projection, that could be very interesting.
    • The deep view would have a surrounding chrome that tallows sorting, filtering and computations.
    • The flat view has no chrome, but is optimised for displaying text, automatically merges cells with identical values, etc.

I wish the Simple Tables would get a bit more TLC in Fibery as even adding a new row is painful. But then again, this all is a different thread. :slightly_smiling_face:

1 Like