英文:
sheets to docs API Request for Access Issue ( Via Method: files.copy )
问题
请审查我的Python代码
get_ipython().system('pip install --upgrade google-api-python-client')
get_ipython().system('pip install google-auth==1.35.0')
get_ipython().system('pip install google-auth-oauthlib==0.4.4')
get_ipython().system('pip install googleapiclient.discovery')
get_ipython().system('googleapiclient.errors import HttpError')
get_ipython().system('pip install pandas')
import json
import pandas as pd
import csv
from googleapiclient.discovery import build
from google.oauth2 import service_account
# 下载 Pygsheets
import pygsheets
# 通过 .JSON 服务帐户密钥进行 Google 云项目身份验证
# 使用先前获取的本地 .json 文件,您可以对您的 Google 服务帐户进行身份验证。
with open('credentials.json') as source:
info = json.load(source)
credentials = service_account.Credentials.from_service_account_info(info)
# 设置凭据和模板文档 ID
credentials_path = '/Users/baker/Desktop/a3/credentials.json'
template_document_id = '12g6okE4Hvr5VCMiGo6Z7szNSp_cZIRafdLHafGdEtRs'
# 从 JSON 文件加载凭据
credentials = service_account.Credentials.from_service_account_file(credentials_path, scopes=['https://www.googleapis.com/auth/documents', 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'])
# 定义常量并构建 API 服务
DOCS_FILE_ID = "12g6okE4Hvr5VCMiGo6Z7szNSp_cZIRafdLHafGdEtRs"
folder_id = '1IWTXGhYhP94MwUj601F7IO_R0ae7wTA7'
SOURCES = ('text', 'sheets')
SOURCE = 'sheets'
COLUMNS = ['to_name', 'to_title', 'to_company', 'to_address']
# 构建 API 服务
DRIVE = build('drive', 'v2', credentials=credentials)
DOCS = build('docs', 'v1', credentials=credentials)
SHEETS = build('sheets', 'v4', credentials=credentials)
# 创建一个新的 Google 表格 + 获取唯一 ID
# 首先,我们将创建一个新的 Google 表格,然后我们将获取该特定 Google 表格的 ID,该 ID 可以在 URL 中找到:
# ![如何获取 Google 表格 ID](https://sempioneer.com/wp-content/uploads/2020/05/2_obtain_the_google_sheet_id.png)
# Google 表格 Python 命令
# 通过 Pyghseets 成功进行 Google 表格身份验证
client = pygsheets.authorize(service_account_file='credentials.json')
# 如何连接到特定的 Google 表格
# 现在,我们已经通过我们的 Google 云项目对 Pygsheets 进行了身份验证,让我们连接到特定的 Google 表格。
# 为了做到这一点,我们需要获取 URL 链接并共享电子表格。
spreadsheet_url = "https://docs.google.com/spreadsheets/d/1SORbxjVtFH4Z6B0kl8qpuGuYrITadhEynlyNQ-MZFOU/edit#gid=1319260213"
test = spreadsheet_url.split('/d/')
SHEETS_FILE_ID = test[1:][0].split('/edit')[0]
# 打开一个电子表格
sheet = client.open_by_key(SHEETS_FILE_ID) # 使用 ID 键选择的方法
# 如何选择特定的 Google 工作表
# 让我们选择自动创建的名为 Sheet1 的工作表:
wks = sheet.worksheet_by_title('FS - JD Salingers')
# 访问行和列的元数据
# 在将数据框上传到 Google 表格后,您可以查看列和行的数量:
print(wks.cols)
print(wks.rows)
# 如何以 Python 格式获取所有 Google 表格值
# 我们还可以将所有值获取为列表的列表:
all_values = wks.get_all_values()
all_values[0]
# 在我的情况下,请注意,这捕获了工作表右侧的所有空白空间:
# 一种快速删除每个列表中的空字符串的方法是进行嵌套列表理解:
# 提取 Google 表格中的特定范围(列和行)
cell_range = wks.range('A1:z10', returnas='matrix')
cell_range
# 提取单个行以提取列标题
headers = wks.get_row(1, include_tailing_empty=False)
print(headers)
# 提取单列
# 有时您可能希望从工作表中选择特定的列:
wks.get_col(2)
# 直接将数据从 Google 表格插入到合并文档
# 基于模板文档创建新的 Google 文档
new_doc_body = {
'title': 'JD Salinger Finished'
}
new_doc = DOCS.documents().create(body=new_doc_body).execute()
new_doc_id = new_doc['documentId']
# 创建批量更新文档的请求列表
requests = []
# 遍历每一行,并将插入文本请求添加到请求列表中
for row in all_values[1:]:
request = {
'insertText': {
'location': {
'index': 1
},
'text': f'{row[0]} - {row[1]} - {row[2]} - {row[3]}\n\n'
}
}
requests.append(request)
# 执行请求以将内容插入文档
DOCS.documents().batchUpdate(documentId=new_doc_id, body={'requests': requests}).execute()
print("已将内容插入新的 Google 文档。")
print(f"已创建新的 Google 文档:https://docs.google.com/document/d/{new_doc_id}")
期望行为
访问生成的链接(即使对链接不请求访问权限,直接访问生成的文档)并将结果邮件合并文件放入我的文件夹。
实际行为
收到以下消息:
"内容已插入新的 Google 文档。已创建新的 Google 文档:https://docs.google.com/document/d/17KXUPXZd1hxcWa4-oaEVzrKaFuCmRlcONtu_3Z_i
英文:
Please review my python code
get_ipython().system('pip install --upgrade google-api-python-client')
get_ipython().system('pip install google-auth==1.35.0')
get_ipython().system('pip install google-auth-oauthlib==0.4.4')
get_ipython().system('pip install googleapiclient.discovery')
get_ipython().system('googleapiclient.errors import HttpError')
get_ipython().system('pip install pandas')
import json
import pandas as pd
import csv
from googleapiclient.discovery import build
from google.oauth2 import service_account
# ## Download Pygsheets
import pygsheets
# ## Authenticating To A Google Cloud Project With A .JSON Service Account Key
# Using a previously obtained local .json file you can authenticate to your google service account.
with open('credentials.json') as source:
info = json.load(source)
credentials = service_account.Credentials.from_service_account_info(info)
# --------------------------------------------------------
# ## Set credentials and Templat Doc ID
credentials_path = '/Users/baker/Desktop/a3/credentials.json'
template_document_id = '12g6okE4Hvr5VCMiGo6Z7szNSp_cZIRafdLHafGdEtRs'
# ## Load credentials from the JSON file
credentials = service_account.Credentials.from_service_account_file( credentials_path, scopes=['https://www.googleapis.com/auth/documents', 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive'] )
# ## Define constants and build API services
DOCS_FILE_ID = "12g6okE4Hvr5VCMiGo6Z7szNSp_cZIRafdLHafGdEtRs"
folder_id = '1IWTXGhYhP94MwUj601F7IO_R0ae7wTA7'
SOURCES = ('text', 'sheets')
SOURCE = 'sheets'
COLUMNS = ['to_name', 'to_title', 'to_company', 'to_address']
# ## Build API Services
DRIVE = build('drive', 'v2', credentials=credentials)
DOCS = build('docs', 'v1', credentials=credentials)
SHEETS = build('sheets', 'v4', credentials=credentials)
# ## Create A New Google Sheet + Obtain The Unique ID
# Firstly we are going to create a new google sheet, then we'll obtain the id of that specific google sheet which can be found within the URL:
# ![how to obtain the google sheet id](https://sempioneer.com/wp-content/uploads/2020/05/2_obtain_the_google_sheet_id.png)
# --------------------------------------------------------------------------------
# ## Google Sheet Wizardry With Python Commands
# ### Authenticating With Google Sheets With Pyghseets
# Let's see how to successfully authenticate to google sheets with our .json key
client = pygsheets.authorize(service_account_file='credentials.json')
# ---------------------------------------------------------------------------------
# ### How To Connect To a Specific Google Sheet
# Now that we've authenticated pygsheets with our google cloud project let's connect to a specific google sheet.
#
# In order to do this we will need to grab the URL link and share the spreadsheet.
spreadsheet_url = \
"https://docs.google.com/spreadsheets/d/1SORbxjVtFH4Z6B0kl8qpuGuYrITadhEynlyNQ-MZFOU/edit#gid=1319260213"
test = spreadsheet_url.split('/d/')
SHEETS_FILE_ID = test[1:][0].split('/edit')[0]
#open a spreadsheet
sheet = client.open_by_key(SHEETS_FILE_ID) #method 1 by selecting by ID Key
# ### How To Select A Specific Google Worksheet
# Let's select the automatically created worksheet called Sheet1:
wks = sheet.worksheet_by_title('FS - JD Salingers')
# ------------------------------------------------------------------------
# ### Accessing Rows & Columns Meta-data
# After uploading a dataframe into a google sheet, you can see how many columns and rows you have with:
#
# ~~~
#
# wks.cols # To view the number of columns
# wks.rows # To view the number of rows
#
# ~~~
print(wks.cols)
print(wks.rows)
# ### How To Get All Of The Google Sheet Values In A Python Format
# We can also get all of the values in a list of lists:
# In[43]:
all_values = wks.get_all_values()
all_values[0]
# In my case, notice how this has picked up all of the empty spaces that are located on the right hand side of the worksheet:
# In[ ]:
# A way to quickly remove the empty strings per list would be to do a <strong> nested list comprehension: </strong>
# In[44]:
[[item for item in row if item] for row in all_values]
# In[ ]:
# ------------------------------------------------
# ### How To Get Cell Ranges In Google Sheets With Python
# You can also extract specific ranges (columns and rows) similar to your excel functions:
# In[36]:
cell_range = wks.range('A1:z10',
returnas='matrix')
# In[37]:
cell_range
# ### How To Get A Single Row For Extracting The Column Headers
# We can get a single row with:
#
# ~~~
#
# wks.get_row(row, returnas='matrix', include_tailing_empty=False, **kwargs)
#
# ~~~
# In[45]:
headers = wks.get_row(1, include_tailing_empty=False)
# In[46]:
print(headers)
# ------------------------------------------------------------------------------------
# ### How To Extract A Single Column
# Sometimes you might want to select a specific column from your worksheet:
# In[47]:
wks.get_col(2)
# ### Directly insert the data from Google Sheets into the merged document
# Create a new Google Docs document based on the template document
new_doc_body = {
'title': 'JD Salinger Finished'
}
new_doc = DOCS.documents().create(body=new_doc_body).execute()
new_doc_id = new_doc['documentId']
# Create the requests list for batch updating the document
requests = []
# Iterate over each row and add the insertText request to the requests list
for row in all_values[1:]:
request = {
'insertText': {
'location': {
'index': 1
},
'text': f'{row[0]} - {row[1]} - {row[2]} - {row[3]}\n\n'
}
}
requests.append(request)
# Execute the requests to insert the content into the document
DOCS.documents().batchUpdate(documentId=new_doc_id, body={'requests': requests}).execute()
print("Content has been inserted into the new Google Docs document.")
print(f"New Google Docs document created: https://docs.google.com/document/d/{new_doc_id}")
Expected Behavior
access to links Produced ( i.e. for the links not to ask for access and direct to the produced docs for me to access) and for the resultant mail merge files to be deposited in my folders.
Actual Behavior
Receive the following message:
Content has been inserted into the new Google Docs document.
New Google Docs document created: https://docs.google.com/document/d/17KXUPXZd1hxcWa4-oaEVzrKaFuCmRlcONtu_3Z_i4wk
like from this angle it looks like my code has run perfectly fine but then. I visit the links and it asks for me to request access even though, I performed all the previous steps using the same Gmail account (i.e. built all the credentials using the same Gmail account, etc.).
答案1
得分: 1
以下是翻译好的部分:
"Taking a look at the code and the notes in here:
# ## Authenticating To A Google Cloud Project With A .JSON Service Account Key
# Using a previously obtained local .json file you can authenticate to your google service account.
with open('credentials.json') as source:
info = json.load(source)
credentials = service_account.Credentials.from_service_account_info(info)
it seems like you are using the service account for authentication, since the files are being created with the service account as an authentication method the owner of the files is very likely the service account itself, if you have a Google Workspace account you could bypass this by using impersonation (Make sure you setup domain wide delegation for your service account first.)
If you don't have a Workspace account maybe changing the approach of authorization would be better, you can authenticate as a specific user through OAuth 2.0, this does require more involvement from a user but it has the advantage of always creating the documents using the credentials of the user who is running the application as they will be prompted to log in."
英文:
Taking a look at the code and the notes in here:
# ## Authenticating To A Google Cloud Project With A .JSON Service Account Key
# Using a previously obtained local .json file you can authenticate to your google service account.
with open('credentials.json') as source:
info = json.load(source)
credentials = service_account.Credentials.from_service_account_info(info)
it seems like you are using the service account for authentication, since the files are being created with the service account as an authentication method the owner of the files is very likely the service account itself, if you have a Google Workspace account you could bypass this by using impersonation (Make sure you setup domain wide delegation for your service account first.)
If you don't have a Workspace account maybe changing the approach of authorization would be better, you can authenticate as a specific user through OAuth 2.0, this does require more involvement from a user but it has the advantage of always creating the documents using the credentials of the user who is running the application as they will be prompted to log in.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论