Tabletop becoming obsolete :-(

[cheelay]

So what exactly is Google doing to make Tabletop obsolete? Is there really no hope that it can be revived after the changes?

I've played with Papa Parse, but it is limited to one sheet. I love that Tabletop can pull multiple sheets into one JSON object.

Thank you,
Nate

[cheelay]

There are a lot of sheet ---> JSON projects around, are they all going to break? That's why I'm wondering exactly what Google is doing that is causing breakage.

[benoitdemaegdt]

Any idea about when it will become obsolete ?

[jsoma]

I believe they'll start turning off the V3 API in May, and then by September it all shuts down? Not sure where I read it, but that's what I remember offhand.

The issue is that the new version of their API requires you to use OAuth to log in to do pretty much anything, so we're kinda stuck with something that works from the "publish as CSV" angle. I haven't played around with it too much other than getting the Papa Parse example up and running, so if anyone has other ideas or approaches I'm all ears and happy to update the README and examples.

[greggrichter]

Here are the details: https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api

[mroswell]

A note that Papa Parse is not limited to one sheet, either using the current README solution, or the solution that I offered at #189 (comment) that fixes the CORS issue.

In the latter solution you specify the worksheet name, as before. In the former solution (which seemed to break early this week--for at least two of us) you add gid= to the URL and add the appropriate sheet id. Also, in the provided solution, you use the Publish ID, and in the one I provided in issue #189 you use the regular worksheet ID. I'm now accessing four sheets from one worksheet with speeds I haven't seen before. And updates seem to happen without much caching delay, as well.

I don't know if the solution I offer is going to cost money. The Google Sheet API v4 does impose limits. (Time will tell, I guess. I couldn't actually figure out how to calculate what I'll be charged. My guess is: not much.)

[jwhazel]

I think that it's important to note that while @mroswell has a nice and elegant solution in #189 (comment) (I'm not even sure it requires Papa Parse since it returns a simple well-formed JSON object), this is probably not a good idea for public facing projects. Or at least it's something that people should consider carefully before choosing to implement.

The purpose of an API key is that it is a key that you hold and keep protected, just like the key to your house or your car. Ideally it should be hidden behind a backend that proxies requests between your client and whatever service you're ingesting so that you have some level of control over who is using it. If you put it in your client facing code, anyone can see it and use it for their own purposes. I've already verified this by searching for repos that use this method posted above, pulling the key, and using it to download my own spreadsheets.

If you do decide to proceed, know that Google does not allow you to cap billing. This is by design. The best you can do is set up alerts when you reach a threshold. Be prepared to react quickly if you get a billing notification. Also, don't commit code that has the API key in it. And if you can, set a referer on any keys you make. None of those things can stop bad people from stealing your keys, but they might slow down the damage a little when it does happen.

[georgio8]

Oh heck! This API key security issue comes very close to being a killer for my project.
I recently built this web app that displays maps (using MapBox) with a pin for displaying data from each row of a GSheet - a kind of replacement for Fusion Tables. Then a couple of weeks later it broke when Google changed the CORS policy for accessing their API.
So I was delighted to see @mroswell nice solution. I have re-engineered my web app to use that and it's working fine again.
But my aim is to offer the app as a tool for all to use and of course as a web app it is simply a JS script with everything including the API key exposed.
I guess the only option I have is to restrict my own Google API key to the domains of websites that I manage or control and to offer a public version of the app that requires as a parameter a different Google API key that is the responsibility of the using organisation (and they would be advised to restrict the key to domains they manage or control). Of course the API keys will still be exposed, but if they're restricted to trusted domains they should be safe from abuse - right?

[mroswell]

@jwhazel the code I posted in comment #189 returns an object, but result.values is an array of arrays.

[jwhazel]

@georgio8

I guess the only option I have is to restrict my own Google API key to the domains of websites that I manage or control and to offer a public version of the app that requires as a parameter a different Google API key that is the responsibility of the using organisation (and they would be advised to restrict the key to domains they manage or control). Of course the API keys will still be exposed, but if they're restricted to trusted domains they should be safe from abuse - right?

Yes and no. Forcing a trusted domain will stop folks from stealing your key and also using it client side. This is of course better than nothing. It can't stop folks from using it server side and spoofing the referer header.

I'm not saying that the client side solution using the key should be taken off the table, just that I think there needs to be a big disclaimer in bold faced print somewhere so people understand the implications of doing it this way. I'm a big fan Tabletop and have been using it for ~10 years now. I plan on using this api key client side for private applications and in build steps where exposing the key has no real consequence. For public facing apps I'm not sure what I'll be doing yet but likely some form of server side code that hides the key and caches the response to an S3 bucket or something.

@mroswell

jwhazel the code I posted in comment #189 returns an object, but result.values is an array of arrays.

All I'm saying is that you don't need Papa Parse or jQuery or any other library for this. It's just an array of arrays in the response. So if you pull the raw data like this let rawData = await fetch(endpoint).then((res) => res.json());, then a couple lines of vanilla JS will give you an array of objects exactly like how Tabletop did it:

  //Use the first row as the header row
  let headers = rawData.values.shift();

  //Now loop through each subsequent row and bind each value to the corresponding header
  let data = rawData.values.map((row) => {
    return row.map((val, i) => {
      return { [headers[i]]: val };
    });
  });

If you want something besides a simple key:value pair with the key being the header value, you can modify that loop however you want. But the point is it's so much faster/simpler and doesn't require any external libraries.

[cheelay]

The migration deadline was pushed back to January 26, 2021.

https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api