How to download a Google Sheet on a cron
Tags: beginners, tutorial, python, learning
Published: 2024-02-22 19:48:56

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

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/juzu9nmca5oqkwsc1t9e.png)

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".

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/adsyceilpudq1yxhk0a6.png)

Then click the Enable APIs & Services button.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o6axs2uks64mermix13h.png)

Then search for "Google Sheets API".

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wngmuj3mvshds6r4g91f.png)

Now enable it for your project.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g9e9mbqk0mab6thwhdse.png)

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".

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/848o6j5xwel7y1shs9dq.png)

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

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2jbkmiad1e48w9dyojtl.png)

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.

![](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6vs3g7a3mi04bwmqmft2.png)

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:

```bash
sudo apt-get install python3 python3-pip
```

Then I installed the Google Client Library

```bash
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.

```python
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.

```bash
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:

```bash
crontab -e
```

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

```bash
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.