1

A friend has given me edit access to a Google Sheet he owns. I want to write to it via web server code hosted elsewhere---at Wix, actually, where I've built a page that wants to update the spreadsheet from time to time, perhaps a dozen updates per day.

I have found several technologies that might solve the problem. Google Apps Script is one, the Sheets API another. The latter of these is (as far as I can tell) really three distinct options depending on authentication scheme: API key, service account, or OAuth2.

The question is this: Given my specific situation, which of these four approaches (or others I haven't found) is feasible and most appropriate? I'm not asking for opinions; I just don't want to go down one path only to learn later that it's an unworkable dead end (as preliminary research suggests that API key might be) or absurd overkill (as preliminary research suggests that OAuth2 with a Google-approved app might be). Note in particular that I have edit access to the spreadsheet in question and can give that access to others if necessary. If the choice depends on factors I haven't mentioned, what are those factors?

user620316
  • 435
  • 4
  • 11
  • 1
    Please add a brief description of your search/research efforts as is suggested in [ask] including adding a clear definintion of what means "easiest way to write programmaticall" and limit it to a single question. – Rubén Sep 26 '20 at 21:21
  • @user620316 - nothing in your question is unfocused, this is an unfortunate change of wording (previously the reason was called "too broad"). You ask for the *easiest* way to do something - this is inherently too broad. Try to rework the question to ask one problem at a time, from what you've asked, a treatise can be written. For example, how about starting with an API key/service account auth considerations? Also note that service account *is* OAuth, but 2-legged, while auth with client giving permissions is a 3-legged one. – Oleg Valter is with Ukraine Sep 27 '20 at 00:20
  • @OlegValter My original question was closed as "unfocused". Per guidelines, I have edited it to add details and improve the focus. Also per guidelines, I will now explain in detail why I think it should be reopened: I am an experienced coder who has never used OAuth2. I have a specific problem to solve, and see that there are several options for solution---but some may not work for my problem, I dunno. Rather than spend perhaps days experimenting and trying to figure out failures that may not be my fault, this site seemed a good place to ask the experts how to get started. – user620316 Sep 27 '20 at 01:13
  • @user620316 I was talking about the specific close reason - "more focused" was called "too broad" before it was decided that old reasons "are not friendly enough", hence the confusion. Also please note that SO is not for asking how to get started, it is for solving specific problems. Anyways, GAS won't work outside Google infrastructure. Sheets API is your choice - just use the client library to manage auth and changes and you will be all set. – Oleg Valter is with Ukraine Sep 27 '20 at 01:25
  • 1
    @OlegValter Thanks for your response. I think that the boundary between "getting started" and "solving specific problems" is not as clear as it might be. For example, suppose I had asked "Can GAS be used in this case"? This seems a very specific problem with a very specific answer, including reason, which you furnished. In any case, I have my answer, so thanks again. – user620316 Sep 27 '20 at 01:44
  • With GAS, You can create a custom api with [webapp](https://stackoverflow.com/search?q=%5Bgoogle-apps-script%5D+*doPost*) or the inbuilt [tag:google-apps-script-api] and post to it. So it can be triggered from outside. Webapp oauth is probably the easiest, but probably the most insecure as well. – TheMaster Sep 27 '20 at 03:49
  • @TheMaster - you mean setting up a Web App? Yeah, might work, but didn't the user620316 ask about "web server code hosted elsewhere" - in that sense using GAS would be completely impossible. Re:secure - technically, a web app can be made open to anyone and receive the token in payload, then manually decode and validate the fields, but I think that is a ridiculous amount of setup for such a task – Oleg Valter is with Ukraine Sep 27 '20 at 11:17
  • @OlegValter Don't see why it is impossible. Although we call it webapp, On GAS side, We just need a `doPost()` function. Nothing else. Then OP's hosted web app can call GAS web app. Re:Secure I'm aware, but That complicated setup is what undermines privacy as it is prone to developer mistakes. – TheMaster Sep 27 '20 at 11:30
  • @TheMaster - yeah, I meant that this would be a native solution (as the web app would do all the work of changing the spreadsheet), if that's the case, sure, that is a possibility. re:security - I know you know :) Just pointing that out for other readers (although I did that once, nothing *that* complicated if following RFC 7519) – Oleg Valter is with Ukraine Sep 27 '20 at 12:01
  • 1
    @user620316 Additionally, I recommend reading [this](https://stackoverflow.com/a/61654179/11551468) for an explanation on OAuth2, what it is, how it's used etc. – Rafa Guillermo Sep 28 '20 at 10:10
  • [Ben Kol](https://stackoverflow.com/users/14387797) posted an [Answer](https://stackoverflow.com/a/65420702) saying "On my Wix site I'm using this NPM package: https://www.npmjs.com/package/google-spreadsheet" – Scratte Dec 23 '20 at 18:33

0 Answers0