我在 Python 中有一个列表,我想把它逐行按顺序写入 Google 表格的第一列中。我已经完成了所有初始的身份验证部分,以下是代码:
credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)
我不知道如何以轻松的方式完成这个任务。
我在 Python 中有一个列表,我想把它逐行按顺序写入 Google 表格的第一列中。我已经完成了所有初始的身份验证部分,以下是代码:
credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)
这个示例脚本怎么样?此示例将list
附加到A列。该列表作为数据是二维数组。请注意这一点。要使用此脚本,请在API控制台中启用Sheet API v4。
credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)
list = [["valuea1"], ["valuea2"], ["valuea3"]]
resource = {
"majorDimension": "ROWS",
"values": list
}
spreadsheetId = "### spreadsheet ID"
range = "Sheet1!A:A";
service.spreadsheets().values().append(
spreadsheetId=spreadsheetId,
range=range,
body=resource,
valueInputOption="USER_ENTERED"
).execute()
my_dict
,只需传递 my_dict.items()
而不是 list
就可以解决问题。 - Akshay Maldhure基于谷歌官方快速入门和@Tanaike的答案,以下是如何将行附加到Sheet文档的示例:
从URL中提取电子表格ID:
脚本:
import os
import pickle
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
SHEETS_READ_WRITE_SCOPE = 'https://www.googleapis.com/auth/spreadsheets'
SCOPES = [SHEETS_READ_WRITE_SCOPE]
def main():
spreadsheet_id = '1TfWKWaWypbq7wc4gbe2eavRBjzuOcpAD028CH4esgKw' # this is part of the url of google
rows = [
["Hello World", "שלום עולם ינעל העולם", ":)"],
["Hello"],
["World"]
]
# -----------
credentials = get_or_create_credentials(scopes=SCOPES) # or use GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)
service.spreadsheets().values().append(
spreadsheetId=spreadsheet_id,
range="Sheet1!A:Z",
body={
"majorDimension": "ROWS",
"values": rows
},
valueInputOption="USER_ENTERED"
).execute()
# Source: https://developers.google.com/sheets/api/quickstart/python
def get_or_create_credentials(scopes):
credentials = None
if os.path.exists('token.pickle'):
with open('token.pickle', 'rb') as token:
credentials = pickle.load(token)
if not credentials or not credentials.valid:
if credentials and credentials.expired and credentials.refresh_token:
credentials.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file('credentials.json', scopes)
credentials = flow.run_local_server(port=0)
with open('token.pickle', 'wb') as token:
pickle.dump(credentials, token)
return credentials
if __name__ == '__main__':
main()
spreadsheet_id = "<你的电子表格文档ID>"
结果:
如果您连续多次运行脚本,它看起来就是这个样子。
请按照Google官方快速入门指南进行操作,并授予API权限并安装这些软件包:
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
这段代码将一个列表的列表附加到 Google Sheets 中的每一列。my_list 作为数据是一个二维数组。
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
class spreadSheetLogger:
def __init__(self, sheetName):
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
credentials = config_file.get_google_credentials()
# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '################'
SAMPLE_RANGE_NAME = '{}!A:A'.format(sheetName, "DefaultName")
service = build('sheets', 'v4', credentials=self.creds)
sheet = self.service.spreadsheets()
def spreadLogger(self, message, requestJson, timestamp)
my_list = [[message], [str(timestamp)], [str(requestJson)]]
body = {
"majorDimension": "COLUMNS",
"values": my_list
}
request = self.service.spreadsheets().values().append(spreadsheetId=self.SAMPLE_SPREADSHEET_ID,range=self.SAMPLE_RANGE_NAME,valueInputOption='RAW', body=body)
response = request.execute()
return "Record Inserted Successfully"
输出:(在Google表格中)
import pandas as pd
import gspread
from gspread_dataframe import set_with_dataframe
from oauth2client.client import GoogleCredentials as GC
df = # YOUR DATAFRAME
document_id = # YOUR DOCUMENT ID
worksheet_name = # YOUR WORKSHEET NAME
gc = gspread.authorize(GC.get_application_default())
doc = gc.open_by_key(document_id)
# Update existing spreadsheet or create a new one
try:
sheet = doc.worksheet(worksheet_name)
except:
sheet = doc.add_worksheet(worksheet_name, rows=1, cols=1)
set_with_dataframe(sheet, df, resize=True)
如果您想了解有关将Python与Google表格配合使用的更多信息,可以查看此文章。
credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)
spreadsheet_id = "give the spreadsheet ID of the sheet you want to append to"
range_name = "specify the range you are looking at eg: A1:B1"
values = [
[list of cell values per row]
[additional rows of data]
]
body = {
'values': values
}
result = service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id, range=range_name,
valueInputOption=value_input_option, body=body).execute()
来自文档:
更新需要一个有效的ValueInputOption参数(对于单个更新,这是必需的查询参数;对于批量更新,此参数在请求正文中是必需的)。 ValueInputOption控制是否解析输入字符串,如下表所述:
RAW 输入不会被解析,只是作为字符串插入,因此
输入“= 1 + 2”将字符串“= 1 + 2”放置在单元格中,而不是公式。 (布尔值或数字等非字符串值始终被处理为RAW。)
USER_ENTERED 输入被解析,就像它被输入一样
进入Google Sheets UI,因此“Mar 1 2016”变成日期,“= 1 + 2” 变成公式。格式也可以被推断出来,因此“$100.15”变成 具有货币格式的数字。
import pandas as pd
import gspread
from gspread_dataframe import set_with_dataframe
from oauth2client.client import GoogleCredentials as GC
my_list = ['apple', 'banana', 'orange']
df_my_list = pd.DataFrame(data=my_list[1:], columns=[my_list[0]])
gc = gspread.authorize(GC.get_application_default())
doc = gc.open_by_key(document_id)
document_id = # YOUR DOCUMENT ID
worksheet_name = 'my_list'
# Try to update current worksheet, or create a new one
try:
sheet = doc.worksheet(worksheet_name)
except:
sheet = doc.add_worksheet(worksheet_name, rows=1, cols=1)
set_with_dataframe(sheet, df_my_list, resize=True)
关于如何将Python与Google Sheets结合使用的更多信息,您可以查看这篇文章。