The new PapaParse solution yields "No 'Access-Control-Allow-Origin' header" error

[jakob1111]

Using the example code at https://github.com/jsoma/tabletop/blob/master/examples/simple/no-tabletop.html yields the following error when I try to load a spreadsheet:

Access to XMLHttpRequest at 'https://doc-0s-4k-sheets.googleusercontent.com/pub/l5l039s6ni5uumqbsj9o11lmdc/bj8mlpr717s5u5a0ves5ooppes/1593142745000/112192102762685134803/*/e@2PACX-1vQB-VAHmJgZQ00hlOGySWx8kd0Cq4z7o1V47juQc3PcTHkCuCNNmd9YxHZW4cnzDjA71UH0eL85VE5i?gid=0&single=true&output=csv' (redirected from 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQB-VAHmJgZQ00hlOGySWx8kd0Cq4z7o1V47juQc3PcTHkCuCNNmd9YxHZW4cnzDjA71UH0eL85VE5i/pub?gid=0&single=true&output=csv') from origin 'https://learning-web.github.io' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource

[mroswell]

I hired a super dev who helped me through that error, enabling successful sheet access via Google API v4 (still using Papa Parse as an aid). I can post solution tomorrow. Google stopped placing CORS headers on CSV. (4 days after I did the recommended update, seemingly successfully. Quite a shock to go from “YAY!” to nothing working at all, four days later.) - Get a Google API key. Instead of the share ID, use the sheet URL ID. - Consult Google API migration docs for how to structure the HTTP request url. - And then use Papa Parse to deal with returned array of arrays. I can post sample working code tomorrow. (I’ll check with dev to see if he wants to be credited.) ------ UPDATE: GitHub notes: "Email replies do not support Markdown." So I've pasted the working solution in the next comment.

[mroswell]

// 1. Enable the Google Sheets API and check the quota for your project at
//    https://console.developers.google.com/apis/api/sheets
// 2. Get an API key. See
//    https://console.developers.google.com/apis/

let SHEET_ID = '---FILL THIS IN---'; // Get this from the main sheet URL (not the copied Publish URL with '2PACX' in it).
let API_KEY = '---FILL THIS IN---';

function fetchSheet({ spreadsheetId, sheetName, apiKey, complete }) {
    let url = `https://sheets.googleapis.com/v4/spreadsheets/${spreadsheetId}/values/${sheetName}?key=${apiKey}`;
    return fetch(url).then(response =>
        response.json().then(result => {
            let data = Papa.parse(Papa.unparse(result.values), { header: true });
            complete(data);
        })
    );
}

function init() {
    fetchSheet({
        spreadsheetId: SHEET_ID,
        sheetName: '---FILL THIS IN---',
        apiKey: API_KEY,
        complete: showInfo
    });
}

Check to see if you have further references to tabletop. For instance, if you have:

function showInfo(sheet_data, tabletop) {
    $.each(tabletop.sheets("SHEET-NAME").all(), function(i, member) {
    ... the rest of your function ...
}

You can replace that with:

function showInfo(results) {
    let data = results.data;
    $.each(data, function(i, member) {
    ... the rest of your function ...
}

Of course, remove the tabletop script, and add the PapaParse script in your HTML file.

<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

You might not need the parse/unparse above, but it worked for me as a direct drop-in to my code.

I'll credit the dev who wrote the functions, with his permission. (I've written to ask.)

He relied on this Google Sheets API migration documentation:
https://developers.google.com/sheets/api/guides/migration
...and this PapaParse documentation:
https://www.papaparse.com/docs

Update: Dev responds:

• Oh, no need to credit me. :-) The solution I gave was optimized for your codebase. I'm sure there is a more elegant general-purpose solution.
• I highly endorse you sharing with the community. 😊🙏✨

[georgio8]

Thanks so much for solving this one. I've been tearing my hair all afternoon over it. My new web app broke only two weeks in, but only for some sheets. No way to know it was down to a Google policy change!
I'm going to apply your fix tomorrow.

[billbrod]

This works for me, thanks! I had to remember to go into the sheet's sharing options and make it so anyone with the link can view before it worked, and it took me far too long to figure out that was why I was getting permissioned denied errors.

[jsoma]

Thank you!! I updated the README with a link to this issue.

[mroswell]

By the way, getting the API key sounds easy, but it is legitimately confusing. This quirky video covers most of the steps:
https://www.youtube.com/watch?v=3jZ5vnv-LZc

(Substitute Google Sheets API for the YouTube Data API v3.)

I found there was a bit of confusion at the end; it didn't grant me a key right away (as the video shows on the YouTube side). I don't remember exactly what finally did the trick. Clarifying comments on that step are welcome.

(The other hard or non-intuitive parts are figuring out in advance what you’ll be charged... and figuring out how to request upgrades to various imposed limits... and figuring out how to protect your key.)

[cha0sg0d]

Is it still possible to access the Google Sheet without needing OAuth? I attempted to to reproduce @mroswell 's solution but in order to get an API key I need OAuth. This would be very frustrating for my app and I'm hoping to avoid it.

Thanks!

[mroswell]

Is it still possible to access the Google Sheet without needing OAuth? I attempted to to reproduce @mroswell 's solution but in order to get an API key I need OAuth. This would be very frustrating for my app and I'm hoping to avoid it.

Thanks!

(Ah, that is the confusing part that I alluded to above. #189 (comment)). I think I just entered the required dialog, and saved without setting up OAuth, and it worked. If that works for you, can you document, and we can add those steps in addition to the YouTube video I referenced to the documentation.

[georgio8]

(Ah, that is the confusing part that I alluded to above. #189 (comment)). I think I just entered the required dialog, and saved without setting up OAuth, and it worked. If that works for you, can you document, and we can add those steps in addition to the YouTube video I referenced to the documentation.

I did the same. At https://console.developers.google.com/apis/credentials use +Create Credentials to create an API Key, restrict it appropriately (taking these comments into account) , copy and use it in your code.

[cha0sg0d]

Is it still possible to access the Google Sheet without needing OAuth? I attempted to to reproduce @mroswell 's solution but in order to get an API key I need OAuth. This would be very frustrating for my app and I'm hoping to avoid it.
Thanks!

(Ah, that is the confusing part that I alluded to above. #189 (comment)). I think I just entered the required dialog, and saved without setting up OAuth, and it worked. If that works for you, can you document, and we can add those steps in addition to the YouTube video I referenced to the documentation.

Documentation that worked for me:
Step 1 : Sign in to Google Console Cloud : https://console.cloud.google.com
Step 2 : Create project and makes sure you see the project name at the top of the dashboard
Step 3 : Click Library and search for the Google Sheets API.
Step 4: Click 'Enable' for the Sheets API
Step 5: ** Ignore the suggested 'Create Credential' pop and instead go to this link: . This will avoid the OAuth annoyance that I ran into.
Step 6: Click 'Create Credential' > API KEY > Paste that key into the code from @mroswell 's solution

I hope this helps!

[willismonroe]

I'm able to get API-free no-cors-issues access to the JSON of a public google sheet using the following url string:
https://spreadsheets.google.com/feeds/list/{doc_id_here}/od6/public/values?alt=json

crucially the doc_id is taken from the sharing screen, not from the "Publish to web" screen.

[mroswell]

I'm able to get API-free no-cors-issues access to the JSON of a public google sheet using the following url string:
https://spreadsheets.google.com/feeds/list/{doc_id_here}/od6/public/values?alt=json

crucially the doc_id is taken from the sharing screen, not from the "Publish to web" screen.

I think that's using Google API v3, instead of 4. Not sure: I'm looking at:

https://developers.google.com/sheets/api/guides/migration#v4-api_9
https://developers.google.com/sheets/api/guides/migration#v3-api_9

(No mention of the "od6" in either documentation version -- though I've seen that before in the real world -- but I don't feel confident that this will work after September.)

[georgio8]

But it definitely is weird. My original code that accesses 'published to the web' Sheets without an API key now works again, having been broken for the last couple of weeks! Maybe someone at Google read this thread and realised that the requiring a key for programmatic access to users' Google Drive data that is meant to be in the public domain is more Apple-like than Google-like.

Meanwhile I now have two versions of my web app with and without API key and I'm not sure which to release! I'd much prefer users to be able to use my app access their sheets without having to sign up for an API key.

[vdsabev]

The comments in this thread helped me immensely, so thanks everyone!

FYI I got my app working by using a Google Sheets API key securely on the server-side with Netlify functions: https://github.com/vdsabev/95-percent/blob/master/api/questions.js - this way I can even massage the data to suit my needs and there's no longer need for Papa Parse. Ironically, the most difficult part about this was navigating the Google API console to create a key.

[jblindberg]

Now that tabletop definitely doesn't work anymore, I tried to use the Papa Parse alternative. But I can't see a way to create an API Key for an existing Google Sheets file that already has an Apps Script project (associated with the Google Cloud Platform Default project). How do I create an API Key for a file and associated project that already exist?

[BriterNC]

This is miracle! Thanks you a lots mroswell! I having trouble that I misunderstand this code at first glanced.
I replace a "sheetName" with my "spreadsheets name" that's made me feel dumb!
After working around 3 hours I finally realized it's a url problem that Google APIs return code: 400.
So I, who's dumb, go Googling for error 400 and search how Sheets API Url works.
I finally found https://developers.google.com/sheets/api/samples/reading.
That indicates that I just need to input a "sheet name" not "spreadsheet name". Damn I'm so dumb.

function init() {
fetchSheet({
spreadsheetId: SHEET_ID,
sheetName: '---FILL THIS IN---',
apiKey: API_KEY,
complete: showInfo
});
}

Well don't be a dumb guy like me. XD
Hope this help someone too. lmao RIP ME.

[hay]

The solution @willismonroe mentions unfortunately doesn't seem to work anymore because the v3 API has been shut down.