How to download a Google Sheet on a cron

Posted on

Working with Google Cloud can be confusing for beginners and sometimes all you need is to manipulate a Google Sheet. I have a couple of use cases for downloading a sheet and uploading a sheet. I haven’t quite figured out the upload part yet, but I have got downloading to work. I’ve decided to write a guide for that to hopefully help others out.

Step 1: Where to run the script

The first thing to figure out is where and how to run this script. I needed to run the script on a cron and have the downloaded CSV available via SFTP so I chose to set up a new Digital Ocean droplet. You could just as easily set this up as a Cloudflare Worker, or an AWS Lambda function if you didn’t need to access the file via SFTP.

Step 2: What language to use

This is really down to personal preference. Google has a variety of client libraries available to use such as Node.js, PHP, and Python. For this project, I decided to go with Python. It’s not a language I code in very often but it’s easy to use and has minimal setup on a blank Ubuntu install on my Digital Ocean droplet.

Step 3: Create a Google Cloud project

To enable us to work with the Google Sheets API we need a Google Cloud project. To create one go to Google Cloud Console, create a new project or select an existing one. Next, we need to enable the relevant API. Click “APIs & Services”.

Then click the Enable APIs & Services button.

Then search for “Google Sheets API”.

Now enable it for your project.

In order to interact with the API, we need a service account. Head back to the APIs & Services page, then click on Credentials in the left-hand sidebar. Click “+ CREATE CREDENTIALS” and select “Service account”.

Fill in the service account details and grant it any role that has permission to access Google Sheets.

After creating the service account, click on it and go to the “Keys” tab. Click “Add Key” and choose “JSON”. A JSON key file will be downloaded. This file contains the credentials your script will use to authenticate. Upload this file to your server and make a note of the path. You’ll need this for later. Make sure to keep this key safe as it grants access to your Google Sheet.

Open your Google Sheet and share it with the email address of the service account you just created, giving it at least “Viewer” permissions.

Step 4: Write some code

We first need to install the necessary packages. Depending on which language you chose, this process will be slightly different. As I chose Python, I’m going to use Pip to install my packages. To install Python 3 and pip on Ubuntu, run the following command:

sudo apt-get install python3 python3-pip

Then I installed the Google Client Library

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Here’s my Python file for downloading the sheet. Make sure to replace:

  • 'path/to/your/service-account-file.json' with the path of your uploaded service account key file.
  • 'your_google_sheet_id_here' with the actual ID of your Google Sheet (found in the sheet’s URL).
  • 'Sheet1' with the name of the sheet (or specific range) you want to download.
  • '/path/to/your/folder/sheet.csv' with the path where you want the CSV file to be saved.
from googleapiclient.discovery import build
from google.oauth2.service_account import Credentials
import requests

# Path to your service account key file
SERVICE_ACCOUNT_FILE = 'path/to/your/service-account-file.json'

# The ID of your Google Sheet and the range you want to download
SHEET_ID = 'your_google_sheet_id_here'
RANGE_NAME = 'Sheet1'  # or whatever your sheet is named

# Authenticate using the service account
credentials = Credentials.from_service_account_file(
        SERVICE_ACCOUNT_FILE, scopes=['https://www.googleapis.com/auth/spreadsheets.readonly'])

service = build('sheets', 'v4', credentials=credentials)

# Make the API call
sheet = service.spreadsheets().values().get(spreadsheetId=SHEET_ID, range=RANGE_NAME).execute()

# Convert to CSV
with open('/path/to/your/folder/sheet.csv', 'w') as f:
    for row in sheet.get('values', []):
        f.write(','.join(row) + '\n')

print('Sheet downloaded as CSV.')

You can test that the script works correctly by running the following snippet and checking that the CSV is created.

python3 /path/to/your/download_sheet.py

Step 5: Schedule the Script Using Cron

Ubuntu includes a cron runner. If you went down the Cloudflare Workers route, you could use a scheduled action. If your function is available via HTTP, there are many free cron runners that can call your endpoint for you.

For Ubuntu, open the crontab file:

crontab -e

Add a line to run your script periodically. For example, to run it daily at 1 AM:

0 1 * * * /usr/bin/python3 /path/to/your/download_sheet.py

Replace /path/to/your/download_sheet.py with the actual path to your Python script.

Conclusion

This is purposefully a very abstract guide to give you a decent starting point to create your own solution for your own needs. If you have any questions, drop me a message and I’ll be more than happy to help. I’d love to see what you make too, so please send them over to me.