标签：表格、仪表板、绘图
Google 表格是一种以电子表格形式存储表格数据的简便方法。 使用 Gradio 和 pandas，可以轻松地从公共或私人 Google 表格中读取数据，然后显示数据或绘制数据。 在这篇博文中，我们将构建一个小型实时仪表板，它会在 Google 表格中的数据更新时进行更新。
Google Sheets are an easy way to store tabular data in the form of spreadsheets. With Gradio and pandas, it's easy to read data from public or private Google Sheets and then display the data or plot it. In this blog post, we'll build a small real-time dashboard, one that updates when the data in the Google Sheets updates.
使用 Gradio 构建仪表板本身只需 9 行 Python 代码，我们最终的仪表板将如下所示：
Building the dashboard itself will just be 9 lines of Python code using Gradio, and our final dashboard will look like this:
先决条件：本指南使用Gradio Blocks ，因此请熟悉 Blocks 类。
Prerequisites: This Guide uses Gradio Blocks, so make you are familiar with the Blocks class.
该过程略有不同，具体取决于你使用的是可公开访问的还是私有的 Google 表格。 我们将涵盖两者，让我们开始吧！
The process is a little different depending on if you are working with a publicly accessible or a private Google Sheet. We'll cover both, so let's get started!
借助pandas 库，从公共 Google 表格构建仪表板非常容易：
Building a dashboard from a public Google Sheet is very easy, thanks to the pandas library:
Get the URL of the Google Sheets that you want to use. To do this, simply go to the Google Sheets, click on the "Share" button in the top-right corner, and then click on the "Get shareable link" button. This will give you a URL that looks something like this:
获取你要使用的 Google 表格的 URL。 为此，只需转到 Google 表格，单击右上角的“共享”按钮，然后单击“获取可共享链接”按钮。 这会给你一个看起来像这样的 URL：
https://docs.google.com/spreadsheets/d/1UoKzzRzOCt-FXLLqDKLbryEKEgllGAQUEJ5qtmmQwpU/edit#gid=0
Now, let's modify this URL and then use it to read the data from the Google Sheets into a Pandas DataFrame. (In the code below, replace the URL variable with the URL of your public Google Sheet):
现在，让我们修改此 URL，然后使用它将 Google 表格中的数据读取到 Pandas DataFrame 中。 （在下面的代码中，将 URL 变量替换为你公开的 Google 表格的 URL）：
import pandas as pd
URL = "https://docs.google.com/spreadsheets/d/1UoKzzRzOCt-FXLLqDKLbryEKEgllGAQUEJ5qtmmQwpU/edit#gid=0"
csv_url = URL.replace('/edit#gid=', '/export?format=csv&gid=')
def get_data():
return pd.read_csv(csv_url)
The data query is a function, which means that it's easy to display it real-time using the the gr.DataFrame component, or plot it real-time using the gr.LinePlot component (of course, depending on the data, a different plot may be appropriate). To do this, just pass the function into the respective components, and set the every parameter based on how frequently (in seconds) you would like the component to refresh. Here's the Gradio code:
数据查询是一个函数，这意味着很容易使用 gr.DataFrame 组件实时显示它，或者使用 gr.LinePlot 组件实时绘制它（当然，根据数据不同，不同的绘图可能是合适的）。 为此，只需将函数传递给相应的组件，并根据你希望组件刷新的频率（以秒为单位）设置 every 参数。 这是 Gradio 代码：
import gradio as gr
with gr.Blocks() as demo:
gr.Markdown("# 📈 Real-Time Line Plot")
with gr.Row():
with gr.Column():
gr.DataFrame(get_data, every=5)
with gr.Column():
gr.LinePlot(get_data, every=5, x="Date", y="Sales", y_title="Sales ($ millions)", overlay_point=True, width=500, height=500)
demo.queue().launch() # Run the demo with queuing enabled
就是这样！ 你有一个每 5 秒刷新一次的仪表板，从你的 Google 表格中提取数据。
And that's it! You have a dashboard that refreshes every 5 seconds, pulling the data from your Google Sheet.
对于私有 Google 表格，此过程需要做更多的工作，但不会太多！ 主要区别在于，现在你必须对自己进行身份验证才能授权访问私人 Google 表格。
For private Google Sheets, the process requires a little more work, but not that much! The key difference is that now, you must authenticate yourself to authorize access to the private Google Sheets.
要对你自己进行身份验证，请从 Google Cloud 获取凭据。 以下是设置谷歌云凭据的方法：
To authenticate yourself, obtain credentials from Google Cloud. Here's how to set up google cloud credentials:
First, log in to your Google Cloud account and go to the Google Cloud Console (https://console.cloud.google.com/)
首先，登录你的 Google Cloud 帐户并转到 Google Cloud Console ( https://console.cloud.google.com/\ )
In the Cloud Console, click on the hamburger menu in the top-left corner and select "APIs & Services" from the menu. If you do not have an existing project, you will need to create one.
在 Cloud Console 中，点击左上角的汉堡菜单，然后从菜单中选择“API 和服务”。 如果你没有现有项目，则需要创建一个。
Then, click the "+ Enabled APIs & services" button, which allows you to enable specific services for your project. Search for "Google Sheets API", click on it, and click the "Enable" button. If you see the "Manage" button, then Google Sheets is already enabled, and you're all set.
然后，单击“+ Enabled APIs & services”按钮，它允许你为项目启用特定服务。 搜索“Google Sheets API”，点击它，然后点击“启用”按钮。 如果你看到“管理”按钮，则表明 Google 表格已启用，一切就绪。
In the APIs & Services menu, click on the "Credentials" tab and then click on the "Create credentials" button.
在 API 和服务菜单中，单击“凭据”选项卡，然后单击“创建凭据”按钮。
In the "Create credentials" dialog, select "Service account key" as the type of credentials to create, and give it a name. Note down the email of the service account
在“创建凭据”对话框中，选择“服务帐户密钥”作为要创建的凭据类型，并为其命名。 记下服务帐户的电子邮件
After selecting the service account, select the "JSON" key type and then click on the "Create" button. This will download the JSON key file containing your credentials to your computer. It will look something like this:
选择服务帐户后，选择“JSON”密钥类型，然后单击“创建”按钮。 这会将包含你的凭据的 JSON 密钥文件下载到你的计算机。 它看起来像这样：
{
"type": "service_account",
"project_id": "your project",
"private_key_id": "your private key id",
"private_key": "private key",
"client_email": "email",
"client_id": "client id",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/email_id"
}
获得凭据 .json 文件后，你可以使用以下步骤查询你的 Google 表格：
Once you have the credentials .json file, you can use the following steps to query your Google Sheet:
Click on the "Share" button in the top-right corner of the Google Sheet. Share the Google Sheets with the email address of the service from Step 5 of authentication subsection (this step is important!). Then click on the "Get shareable link" button. This will give you a URL that looks something like this:
单击 Google 表格右上角的“共享”按钮。 与身份验证小节第 5 步中的服务电子邮件地址共享 Google 表格（这一步很重要！）。 然后单击“获取共享链接”按钮。 这会给你一个看起来像这样的 URL：
https://docs.google.com/spreadsheets/d/1UoKzzRzOCt-FXLLqDKLbryEKEgllGAQUEJ5qtmmQwpU/edit#gid=0
Install the gspread library, which makes it easy to work with the Google Sheets API in Python by running in the terminal: pip install gspread
安装gspread 库，通过在终端中运行，可以轻松地在 Python 中使用Google Sheets API ： pip install gspread
Write a function to load the data from the Google Sheet, like this (replace the URL variable with the URL of your private Google Sheet):
编写一个函数来从 Google 表格加载数据，如下所示（将 URL 变量替换为你的私人 Google 表格的 URL）：
import gspread
import pandas as pd
# Authenticate with Google and get the sheet
URL = 'https://docs.google.com/spreadsheets/d/1_91Vps76SKOdDQ8cFxZQdgjTJiz23375sAT7vPvaj4k/edit#gid=0'
gc = gspread.service_account("path/to/key.json")
sh = gc.open_by_url(URL)
worksheet = sh.sheet1
def get_data():
values = worksheet.get_all_values()
df = pd.DataFrame(values[1:], columns=values[0])
return df
The data query is a function, which means that it's easy to display it real-time using the the gr.DataFrame component, or plot it real-time using the gr.LinePlot component (of course, depending on the data, a different plot may be appropriate). To do this, we just pass the function into the respective components, and set the every parameter based on how frequently (in seconds) we would like the component to refresh. Here's the Gradio code:
数据查询是一个函数，这意味着很容易使用 gr.DataFrame 组件实时显示它，或者使用 gr.LinePlot 组件实时绘制它（当然，根据数据不同，不同的绘图可能是合适的）。 为此，我们只需将函数传递给相应的组件，并根据我们希望组件刷新的频率（以秒为单位）设置 every 参数。 这是 Gradio 代码：
import gradio as gr
with gr.Blocks() as demo:
gr.Markdown("# 📈 Real-Time Line Plot")
with gr.Row():
with gr.Column():
gr.DataFrame(get_data, every=5)
with gr.Column():
gr.LinePlot(get_data, every=5, x="Date", y="Sales", y_title="Sales ($ millions)", overlay_point=True, width=500, height=500)
demo.queue().launch() # Run the demo with queuing enabled
你现在拥有一个每 5 秒刷新一次的仪表板，从你的 Google 表格中提取数据。
You now have a Dashboard that refreshes every 5 seconds, pulling the data from your Google Sheet.
这就是它的全部！ 只需几行代码，你就可以使用 gradio 和其他库从公共或私有 Google 表格中读取数据，然后在实时仪表板中显示和绘制数据。
And that's all there is to it! With just a few lines of code, you can use gradio and other libraries to read data from a public or private Google Sheet and then display and plot the data in a real-time dashboard.