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

Recommended Posts

How to copy and paste the contents of a sheet in Google Spreadsheet in JSON format (using Google Colab)
How to get the notebook name you are currently using in Google Colab
How to count the number of elements in Django and output to a template
[Ubuntu] How to delete the entire contents of a directory
How to connect the contents of a list into a string
[Super easy! ] How to display the contents of dictionaries and lists including Japanese in Python
How to easily draw the structure of a neural network on Google Colaboratory using "convnet-drawer"
How to determine the existence of a selenium element in Python
How to check the memory size of a variable in Python
[TensorFlow 2] How to check the contents of Tensor in graph mode
How to format a table using Pandas apply, pivot and swaplevel
How to get the vertex coordinates of a feature in ArcPy
Create a function to get the contents of the database in Go
How to return the data contained in django model in json format and map it on leaflet
[Colab] How to copy a huge dataset
How to compare if the contents of the objects in scipy.sparse.csr_matrix are the same
[Rails 6] Embed Google Map in the app and add a marker to the entered address. [Confirmation of details]
How to reflect validation error and input contents on the previous page without using render in the action
How to pass the execution result of a shell command in a list in Python
How to calculate the volatility of a brand
How to mention a user group in slack notification, how to check the id of the user group
The story of creating Botonyan that returns the contents of Google Docs in response to a specific keyword on Slack
[Circuit x Python] How to find the transfer function of a circuit using Lcapy
How to create a JSON file in Python
How to access the contents of a Linux disk on a Mac (but read-only)
Make a copy of the list in Python
How to save the feature point information of an image in a file and use it for matching
A memorandum of how to execute the! Sudo magic command in Jupyter Notebook
I'm addicted to the difference in how Flask and Django receive JSON data
How to display videos inline in Google Colab
The first step to log analysis (how to format and put log data in Pandas)
Instructions for connecting Google Colab. To the local runtime in a Windows environment
How to get a list of files in the same directory with python
How to display the modification date of a file in C language up to nanoseconds
I tried to notify the update of "Become a novelist" using "IFTTT" and "Become a novelist API"
How to identify the element with the smallest number of characters in a Python list?
How to unit test a function containing the current time using freezegun in python
Use slackbot as a relay and return from bottle to slack in json format.
How to confirm the Persival theorem using the Fourier transform (FFT) of matplotlib and scipy
How to check in Python if one of the elements of a list is in another list
[Implementation explanation] How to use the Japanese version of BERT in Google Colaboratory (PyTorch)
Memorandum (in openpyxl ① copy and paste from another book ② refer to the comparison table)
How to write a GUI using the maya command
How to run AutoGluon in Google Colab GPU environment
How to get the number of digits in Python
How to execute a command using subprocess in Python
To extract the data of a specific column in a specific sheet in multiple Excel files at once and put the data in each column in one row
[Linux] [C / C ++] How to get the return address value of a function and the function name of the caller
[Golang] Command to check the supported GOOS and GOARCH in a list (Check the supported platforms of the build)
How to display the regional mesh of the official statistics window (eStat) in a web browser
How to create an instance of a particular class from dict using __new__ () in python
[Python] How to delete rows and columns in a table (list of drop method options)
How to quickly count the frequency of appearance of characters from a character string in Python?
How to check if the contents of the dictionary are the same in Python by hash value
How to plot the distribution of bacterial composition from Qiime2 analysis data in a box plot
How to insert a specific process at the start and end of spider with scrapy
How to pass the execution result of a shell command in a list in Python (non-blocking version)
A story about porting the code of "Try and understand how Linux works" to Rust
How to find the optimal number of clusters in k-means
How to output a document in pdf format with Sphinx
How to use the __call__ method in a Python class