How to copy and paste the contents of a sheet in Google Spreadsheet in JSON format (using Google Colab)

[Copy and paste is possible] How to download Google Spreadsheet in JSON format

First from the code. Just open Google Colab and copy and paste the two codes below to download the values in the specified sheet locally on your PC in JSON format.

1. 1. Authentication

When you execute the code below on Colab, a link for authentication will be displayed, so follow the instructions on the screen.

from google.colab import auth
from oauth2client.client import GoogleCredentials
import gspread

auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

2. Download in JSON format

Copy and paste the code below into Colab, rewrite the filename of the spreadsheet you want to download and the sheet name you want to download in JSON format, and then execute it. The JSON file will be downloaded to your local PC.

import os
import json
from google.colab import files

ss_name = "File name of the spreadsheet you want to download"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Sheet name you want to download in JSON format")
dict_list = worksheet.get_all_records()

temp_file_path = worksheet.title + '.json'

with open(temp_file_path, 'w') as f:
  f.write(json.dumps(dict_list))

files.download(temp_file_path)

os.remove(temp_file_path)

Commentary

I will write down something like a brief explanation about this process.

This process is executed using a library called gspread that is installed by default in Colab.

By using get_all_records () of gspread, you can get the values in the sheet as a dict (dictionary) list, so create JSON data based on that and then write it to a file once. After that, I downloaded it locally. The exported file is deleted at the end, so it can be executed over and over again.

Finally

Another way to download the contents of Google Sheets in JSON format is to write the process in Google Apps Script and execute it, but with Google Colab, you can download the JSON file in about 1 minute by copying these codes. Can be realized, so when you come up with it, you can execute it quickly and conveniently! I thought, so I wrote it here.

If anyone knows how to download it in JSON format more easily, I would appreciate it if you could comment!

I wrote about how to handle Google Spreadsheets from Google Colab in Qiita before, so I will introduce it as well. I hope you can use it when arranging the code written above. Sample code summary when working with Google Spreadsheets from Google Colab