配置Google API，用JavaScript和Python读取Google sheet里的数据

[发布时间是2024年8月。技术流程可能会存在一些变动]

源代码可以参考：victorspaceRMW/Read-Google-Sheet-with-API: The source code to read the Google Sheet with Google cloud API (github.com)

开头提醒一下各位公司，国内包括腾讯云华为云阿里云，国外包括AWS和GCP，希望你们在开发产品的同时把这方面的文档写的更清楚明白一些。

现在，随着办公逐渐云化，从云上读取数据是一件越来越普遍的事情。用JS或者Python从本地读文件，操作自不必说。

那么，如何从云上，比如Google Sheet里面去读取文件呢？

这看似是个非常简单的工作，但是其实第一次做，非常麻烦。在这里，我们分别给出如何在JS（开发环境选择CodePen）和Python（开发环境选择Anaconda Spyder）当中进行开发的方法。

**[Published in August 2024. The technical process may undergo some changes.]**

You can refer to the source code: victorspaceRMW/Read-Google-Sheet-with-API: The source code to read the Google Sheet with Google Cloud API (github.com).

I would like to remind all companies, both Chinese domestic ones like Tencent Cloud, Huawei Cloud, Alibaba Cloud, and world international ones like AWS and GCP, to make their documentation clearer and more comprehensive while developing their products.

As office work increasingly moves to the cloud, reading data from the cloud has become more common. Reading files from a local machine using JS or Python is straightforward.

So, how can you read files from the cloud, such as from Google Sheets?

This may seem like a very simple task, but in reality, it can be quite troublesome the first time you do it. Here, we will provide methods for development in both JS (using CodePen as the development environment) and Python (using Anaconda Spyder as the development environment).

（1）. 在JS中做开发

在这里我们选择CodePen开发环境：

codePen，一个非常好的做前端开发的工具。

这里，我们首先开始学习如何配置在JavaScript环境中读取GoogleSheet。

首先大家在Google cloud console上注册一个自己的账号。流程在这里：

创建服务帐号 - Google Workspace 管理员帮助

注册的时候，把API类型搞成对应google sheet的那一类API即可。

首先提醒一下大家，google的文档写的非常模糊，整个操作界面也非常不清楚。

当我们已经注册好自己的账号以后，就可以开始创建一个自己的API，然后通过调用这个API来在JS环境里读取google sheet了。

具体的操作如下：

(1) Development in JS

For this, we will use the CodePen development environment:

CodePen is a great tool for front-end development.

Here, we will first learn how to configure reading from Google Sheets in a JavaScript environment.

First, everyone should register for their own account on the Google Cloud Console. The process is outlined here:

Create a Service Account - Google Workspace Admin Help

When registering, make sure to select the API type corresponding to Google Sheets.

Please note that Google’s documentation is quite vague, and the entire operation interface is very unclear.

Once you have registered your account, you can start creating your own API and use it to read Google Sheets in the JS environment.

The specific steps are as follows:

在这里，我们创建一个API密钥。

然后在底下，我们可以显示密钥的值：

Here, we create an API key.

Then, below, we can display the value of the key:

在这里，点击显示密钥，就可以把这个API的数值copy下来。

那么，在JS里，如何读取我们想要读取的表呢？在这里我们附上这部分函数：

API我们都知道是在哪里。但是google sheet的ID（在下面这个代码里是SPREADSHEET_ID）在哪里读呢？

Here, click "Show Key" to copy the value of the API key.

So, how do we read the table we want in JS? We include this part of the function here:

We know where the API is. But where do we find the Google Sheets ID (referred to as SPREADSHEET_ID in the code below)?

就是在这个地方。大家看到了么？

It is here! Do you see it?

    const API_KEY = '你的API'; // 替换为你的Google API密钥
    const SPREADSHEET_ID = '你的表格的ID';//替换成你的表格的ID

    // 异步函数，用于获取表格名称
    async function fetchSheetNames() {
      const url = `https://sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}?key=${API_KEY}`;
      try {
        const response = await fetch(url);
        if (!response.ok) {
          const errorDetails = await response.json();
          throw new Error(`HTTP错误！状态：${response.status}, 信息：${errorDetails.error.message}`);
        }
        const data = await response.json();
        if (data.error) {
          throw new Error(`API错误！信息：${data.error.message}`);
        }
        return data.sheets.map(sheet => sheet.properties.title);
      } catch (error) {
        console.error('获取表格名称时出错：', error);
      }
    }

    // 异步函数，用于获取表格数据
    async function fetchSheetData(sheetName) {
      const range = `${sheetName}!A1:Z100000`; // 根据你的表格调整范围
      const url = `https://sheets.googleapis.com/v4/spreadsheets/${SPREADSHEET_ID}/values/${range}?key=${API_KEY}`;

      try {
        const response = await fetch(url);
        if (!response.ok) {
          const errorDetails = await response.json();
          throw new Error(`HTTP错误！状态：${response.status}, 信息：${errorDetails.error.message}`);
        }
        const data = await response.json();
        if (data.error) {
          throw new Error(`API错误！信息：${data.error.message}`);
        }
        return data.values;
      } catch (error) {
        console.error('获取数据时出错：', error);
      }
    }

（2）. 在Python中做开发

真心恳求google或者其他公司以后可以把自己的文档写的稍微清楚一点。这本来应该不是个特别麻烦的事儿。这个API搞的我已经不太让我相信他是google做的产品了。用户体验还不如高德。

与在JS中调用API相比，Python要调用这个API，需要生成一个.json文件然后放在本地。

首先，我们需要利用pip或者conda安装两个需要的库：

(2) Development in Python

I sincerely hope that Google or other companies will improve their documentation to be a bit clearer in the future. This should not be such a complicated task. This API has made me question if it is indeed a Google product. The user experience is even worse than Gaode（Amap, A Chinese unicorn of geo-info services）’s.

Compared to calling the API in JS, in Python, you need to generate a .json file and place it locally.

First, we need to install the two required libraries using pip or conda:

pip install gspread oauth2client

安装好以后重新启动kernel。

之后，我们在google cloud console上生成.json 文件。

调到这个地方：打开IAM和管理，然后点开“服务账号”这个地方。

点击添加密钥。

After installation, restart the kernel.

Next, generate the .json file on the Google Cloud Console.

Go to this section: Open IAM & Admin, and then click on "Service Accounts."

Click "Add Key."

打开以后到这个地方，把那个JSON文件下载下来。

JSON 是 “JavaScript Object Notation” 的缩写。它是一种轻量级的数据交换格式，易于人类阅读和编写，同时也易于机器解析和生成。JSON 通常用于在服务器和 Web 应用程序之间传输数据。它以键值对的形式组织数据，类似于 JavaScript 中的对象表示法。JSON 的结构简单，广泛用于各种编程语言和平台。（GPT老师的讲解）

下载好这个.json文件以后把它和Python文件放在同一个directory下面：

After opening, go to this section and download the JSON file.

JSON stands for “JavaScript Object Notation.” It is a lightweight data interchange format that is easy for humans to read and write, and easy for machines to parse and generate. JSON is commonly used to transfer data between a server and a web application. It organizes data in key-value pairs, similar to object notation in JavaScript. JSON's structure is simple and widely used across various programming languages and platforms. (Explanation by GPT)

After downloading the .json file, place it in the same directory as your Python file:

之后我们就可以在Python里面写代码调用google sheet了。

Then we could use Google sheet API in our Python code.

# -*- coding: utf-8 -*-
"""
Created on Mon Aug 12 17:42:09 2024

@author: Brian Wang
"""

import numpy as np
import pandas as pd
from datetime import datetime
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Define API scope
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]

# Authorize using the service account JSON key file
creds = ServiceAccountCredentials.from_json_keyfile_name('你那个json文件的名字', scope)

# Log in with the authorized credentials
client = gspread.authorize(creds)

# Open the Google Sheets file
spreadsheet_id = '要读取的表的ID'
sheet = client.open_by_key(spreadsheet_id)

# Select the worksheet to read
worksheet = sheet.sheet1  # or you can use sheet.get_worksheet(index)

# Get all data
data = worksheet.get_all_records()

# Convert data to Pandas DataFrame
df = pd.DataFrame(data)

好了，今天的讲解就到这里！

Cheers! That is all for today!