Download Google Sheet as CSV From URL Using Javascript

Question

Apologies, I would normally hope to pose a more complete and less noobish question, but I'm not very familiar with Javascript and think I failed early on on this one.

I found a nifty Stack Overflow page that had a URL format for getting a CSV file using a GET request from a google sheet: How to export a csv from Google Sheet API?

Supposedly you can plug your sheet ID into this URL format:

https://docs.google.com/spreadsheets/d/{{ID}}/gviz/tq?tqx=out:csv&sheet={{sheet_name}}

and then receive back the CSV data.

If I do this in the browser address bar, I get back a downloadable CSV file and file save prompt, and notice in the headers it's being marked as an attachment.

If I happen to plug this in somewhere else, like Powershell's Invoke-RestMethod, I get back just the CSV seperated values, which is what i would want.

If I try to write a javascript function to do it, it fails.

Here are 2 functions I tried that I couldn't get to work:

async function fetchAsync (url) {

let response = await fetch(url);

let data = await response.json();

return data;

}

function httpGet(theUrl)

{

var xmlHttp = new XMLHttpRequest();

xmlHttp.open( "GET", theUrl, false ); // false for synchronous request

xmlHttp.send( null );

return xmlHttp.responseText;

}

Both times I get a 'failed to load' or 'failed to fetch' error in the console and see nothing on the Network tab, and I can't figure out why.

Can someone let me know if there's something simple i'm missing? I have also tried using this format:

requests.get('https://docs.google.com/spreadsheet/ccc?key=(KEY_HERE)&output=csv')

To be clear, my goal is that I want to get the values from the google spreadsheet, in csv format, into a variable that I can then manipulate elsewhere in my script.

score 1 · Answer 1 · answered Nov 16 '22 at 20:41

I was able to find a solution here: https://saracha-t.medium.com/read-csv-with-javascript-fetch-dabcb0d1fba4

This code worked:

try {
    //read .csv file on a server
    const target = `URLHERE`;

    const res = await fetch(target, {
        method: 'get',
        headers: {
            'content-type': 'text/csv;charset=UTF-8',
        }
    });

    if (res.status === 200) {
        const data = await res.text();
        console.log(data);

    } else {
        console.log(`Error code ${res.status}`);
    }
} catch (err) {
    console.log(err)
}

Download Google Sheet as CSV From URL Using Javascript

1 Answers1