AJ ONeal October 18, 2021

How to Download a (multi-sheet) CSV from Google Sheets (2022 Update)

TL;DR

curl -fsSL "https://docs.google.com/spreadsheets/d/${my_key}/export?format=csv&usp=sharing&gid=${my_sheet}"

This is a real, working example, with multiple sheets.

If you click Share on a spreadsheet in Google Drive, you’ll get a link that you can convert into a CSV download link. It looks like this:

Open in Google Drive: https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/edit#gid=1440123577
Download as CSV: https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/export?format=csv&usp=sharing&gid=1440123577

Note: To select a specific sheet you may need to manually click on the desired sheet’s tab and then manually select and copy the #gid=0000 part from the browser’s URL.

cURL

The quick, working example:

curl -fsSL "https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/export?format=csv&usp=sharing&gid=1440123577"

Download CSV from share link:

# The link, as copied from Google Sheets
my_shared_link='https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/edit?usp=sharing#gid=1440123577'

# The key, extracted from the link: 1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34 
my_key="$(echo "${my_shared_link}" | cut -d'/' -f6)"

# The sheet, extracted from the link: 1440123577
my_sheet="$(echo "${my_shared_link}" | cut -d'#' -f2 | cut -d '=' -f2)"

# The OLD curl command, for reference
# curl -fsSL -o test.csv "https://docs.google.com/spreadsheet/ccc?key=${my_key}&usp=sharing&output=csv"

# The current version, translatable into ANY programming language
curl -fsSL -o test.csv "https://docs.google.com/spreadsheets/d/${my_key}/export?format=csv&usp=sharing&gid=${my_sheet}"

# The proof it still works in 2022
cat test.csv

Node.js

The quick, working example:

"use strict";

let resp = await require("@root/request")({
  url: "https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/export?format=csv&usp=sharing&gid=1440123577",
});
console.log(resp.body);

Download CSV from share link:

"use strict";

let request = require("@root/request");

let shareLink =
  process.argv[2] ||
  "https://docs.google.com/spreadsheets/d/1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34/edit?usp=sharing#gid=1440123577";

let out = process.argv[3] || "./test.csv";

let url = new URL(shareLink);
// 1KdNsc63pk0QRerWDPcIL9cMnGQlG-9Ue9Jlf0PAAA34
let key = url.pathname.split("/")[3];
// 1440123577
let sheet = '';
if (/gid/.test(url.hash) {
  sheet = url.hash.replace(/#gid=(\w+)\b.*/, "$1");
}

async function main() {
  let query = {
    format: "csv",
    usp: "sharing",
  };
  if (sheet) {
    query.gid = sheet;
  }
  let search = new URLSearchParams(query).toString();
  let csvUrl = `https://docs.google.com/spreadsheets/d/${key}/export?${search}`;
  console.info(`Downloading ${csvUrl} ...`);
  let resp = await request({
    url: csvUrl,
    stream: out,
  });

  await resp.stream;

  console.info(`Wrote ${out}`);
}

main().catch(function (err) {
  cosole.error("Fail:", err);
  process.exit(1);
});

References

Categories:

Web Development