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:
# 
# --------------------------------------------------------------------------------
# ## 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.).
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.