Google-sheets – Export CSV from a Google Spreadsheet page from command-line

automationgoogle sheetslinux

I'm using a Google Spreadsheet document to configure a data-driven program.

The program consumes a CSV file, which now I have to export manually from the document. It is rather inconvenient, as everything else is automated.

I'd like to export the CSV file using a bash-script.

How do I do that?

Best Answer

Doing it from bash is very straightforward. All you need to do is run:

curl -s -d /dev/null https://docs.google.com/spreadsheets/d/DOCUMENT_ID/export?exportFormat=csv > mydocument.csv
dos2unix mydocument.csv 2> /dev/null

This only works for publicly shared documents though. To have access to private documents you will need to use OAuth2. You can find information about doing that here and then you can Google how to use OAuth2 with curl. (Because for the time being I can only post 1 URL at a time)

Related Solutions

Google Sheets – Text Cell Formatting Question

I've created this little script to format the input:

function SCHANGE(array) {
  if(typeof(array) == 'number') {
    var number = array.toString();
  } else {
    var number = array.replace(/\D/g,'');
  }

  if(number.length != 12) {
    return "<> 12"
  } else {
    var first = number.substring(0,4);
    var mid = number.substring(4,8);
    var last = number.substring(8,12);
    var formText = first + " - " + mid + " - " + last;
  }
  return formText;
}

The script validates the input and converts it, checks whether 12 digits are given and returns the outcome. I've prepared an example file for you to play around with: formatted text.

Google Sheets – Pull CSV Data from URL

Drop this formula in the first cell of your google spreadsheet:

=importData("http://example.com/activities.csv")

And it will automatically fill out the rest of the current spreadsheet with as many columns and rows as it needs until all the data from the original source csv is displayed.

There are many other powerful ways to feed Google Spreadsheets from all kinds of external sources. Check out this article for some demonstrations.

Best Answer

Related Solutions

Google Sheets – Text Cell Formatting Question

Google Sheets – Pull CSV Data from URL

Related Topic