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://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-1024x536.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://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-2-1024x536.png)
Then click the Enable APIs & Services button.
![](https://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-3-1024x537.png)
Then search for “Google Sheets API”.
![](https://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-4-1024x536.png)
Now enable it for your project.
![](https://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-5-1024x536.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://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-1-1024x537.png)
Fill in the service account details and grant it any role that has permission to access Google Sheets.
![](https://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-cloud-console-7-1024x521.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://www.joebaileyphotography.com/Blog/wp-content/uploads/2024/02/google-sheets.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:
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.