0

Is there a way to modify the url to return json or should I just use something like Puppeteer to scrape the data? It's a public sheet but I don't have access to the sheet itself.

The sheet url I have is something like https://docs.google.com/spreadsheets/d/e/GOOGLE_SHEET_CODE/pubhtml

James
  • 105
  • 9
  • I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. In your question, the sample input value is `https://docs.google.com/spreadsheets/d/e/GOOGLE_SHEET_CODE/pubhtml`? If my understanding is correct, can you provide the sample output values you expect? – Tanaike Feb 25 '23 at 01:12
  • No worries. Yeah I'm just wondering if there's a way to modify the url to return json but seems I'll likely have to use a library to parse the html to json. – James Feb 25 '23 at 01:21
  • Thank you for replying. Although unfortunately, I couldn't understand your expected value, in your situation, do you know the Spreadsheet ID? And, about `It's a public sheet`, in this case, is the Spreadsheet also shared publicly by the viewer? And, from your tag of `javascript`, can you provide your current script? – Tanaike Feb 25 '23 at 01:28
  • It's not really a JS issue, I'll remove that tag. – James Feb 25 '23 at 01:29
  • 1
    Wouldn't it be much easier to convert it to csv? E.g. in `https://docs.google.com/spreadsheets/d/e/GOOGLE_SHEET_CODE/pubhtml`, replace `pubhtml` with `pub?output=csv`. Then you don't need to parse the HTML. – Nick ODell Feb 25 '23 at 01:52
  • 2
    Related? [Did Google Sheets stop allowing json access?](https://stackoverflow.com/questions/68854198/did-google-sheets-stop-allowing-json-access) – ggorlen Feb 25 '23 at 02:00
  • @ggorlen Kinda but I was hoping for an easier method than using the Google API. – James Feb 25 '23 at 02:53
  • @NickODell Thanks! That definitely seems to work a bit better than trying to parse the html. – James Feb 25 '23 at 02:54
  • 2
    @James the link shows multiple solutions beyond the API. I mentioned the CSV method in a comment, but here's a direct link: [How to export a csv from Google Sheet API?](https://stackoverflow.com/questions/37705553/how-to-export-a-csv-from-google-sheet-api/61107170#61107170) – ggorlen Feb 25 '23 at 04:37

0 Answers0