I've got solution to add table without border suggested by Tanaike but I'm still facing issues in indexing.
I want to insert data in the document in the following order (function - insert_data(file_id)) -
The code I'm trying is-
import io
from gdoctableapppy import gdoctableapp
SERVICE_FILENAME = 'C:/Users/XYZ/Testpython/service_account.json' # set path to service account filename
from googleapiclient.discovery import build
from google.oauth2 import service_account
from googleapiclient.http import MediaIoBaseDownload, MediaFileUpload
credentials = service_account.Credentials.from_service_account_file(SERVICE_FILENAME,
scopes=['https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/documents']
)
docs = build('docs', 'v1', credentials=credentials)
drive = build('drive', 'v3', credentials=credentials)
def create_file(file_name):
file_metadata = {
"title": file_name,
"body": {}
}
file = docs.documents().create(body=file_metadata).execute()
print('File ID: %s' % file.get('documentId'))
file_id = file.get('documentId')
try:
permission = {
"role": "writer",
"type": "user",
'emailAddress': '[email protected]'
}
result = drive.permissions().create(fileId=file_id, body=permission).execute()
print(result)
return file_id
except Exception as e:
print('An error occurred:', e)
return None
def insert_data(file_id):
requests = []
values = [['Name of the Client/Organization', 'XYZ'], ['Industry', 'Software']]
requests.append(insert_table_data(file_id, values, index=3))
values2 = [['Country', 'India'], ['State', 'UP']]
requests.append(insert_table_data(file_id, values2, index=5))
requests.append(insert_image(index=1))
requests.append(insert_text(2, '\ntext\n'))
requests.append(insert_text(4, '\nDemo text\n'))
requests.append(insert_text(6, '\n'))
requests.append(insert_image(index=7))
result = docs.documents().batchUpdate(documentId=file_id, body={'requests': requests}).execute()
def insert_image(index):
image_data = {
'insertInlineImage': {
'location': {
'index': index
},
'uri':
'https://www.oberlo.com/media/1603970279-pexels-photo-3.jpg?fit=max&fm=jpg&w=1824',
'objectSize': {
'height': {
'magnitude': 350,
'unit': 'PT'
},
'width': {
'magnitude': 350,
'unit': 'PT'
}
}
}
}
return image_data
def insert_text(index, text):
text_data = {
"insertText":
{
"text": text,
"location":
{
"index": index
}
}
}
return text_data
def insert_table_data(file_id, values, index):
documentId = file_id
resource = {
"oauth2": credentials,
"documentId": documentId,
"rows": len(values),
"columns": len(values[0]),
# "append": True,
"createIndex": index,
"values": values,
}
gdoctableapp.CreateTable(resource)
resource = {
"oauth2": credentials,
"documentId": documentId,
}
res = gdoctableapp.GetTables(resource)
obj = {"color": {"color": {}}, "dashStyle": "SOLID", "width": {"magnitude": 0, "unit": "PT"}}
data = {
"updateTableCellStyle": {
"tableCellStyle": {
"borderBottom": obj,
"borderTop": obj,
"borderLeft": obj,
"borderRight": obj,
},
"tableStartLocation": {
"index": res['tables'][-1]['tablePosition']['startIndex']
},
"fields": "borderBottom,borderTop,borderLeft,borderRight"
}
}
# docs.documents().batchUpdate(documentId=documentId, body={'requests': requests}).execute()
return data
def download_as_docx(file_id):
results = drive.files().get(fileId=file_id, fields="id, name, mimeType, createdTime").execute()
docMimeType = results['mimeType']
mimeTypeMatchup = {
"application/vnd.google-apps.document": {
"exportType": "application/vnd.openxmlformats-officedocument.wordprocessingml.document", "docExt": "docx"
}
}
exportMimeType = mimeTypeMatchup[docMimeType]['exportType']
# docExt = mimeTypeMatchup[docMimeType]['docExt']
docName = results['name']
request = drive.files().export_media(fileId=file_id,
mimeType=exportMimeType) # Export formats : https://developers.google.com/drive/api/v3/ref-export-formats
# fh = io.FileIO(docName + "." + docExt, mode='w')
fh = io.FileIO(docName, mode='w')
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print("Download %d%%." % int(status.progress() * 100))
def download_as_pdf(file_id, file_name):
request = drive.files().export_media(fileId=file_id,
mimeType='application/pdf')
fh = io.BytesIO()
downloader = MediaIoBaseDownload(fh, request)
done = False
while done is False:
status, done = downloader.next_chunk()
print("Download %d%%." % int(status.progress() * 100))
fh.seek(0)
filename = file_name.split('.docx')[0] + '.pdf'
with open(filename, 'wb') as fx:
fx.write(fh.getvalue())
def delete_gdrive_file(file_id):
"""Deleted file on Google Drive
:param file_id: ID of Google Drive file
"""
response = drive.files().delete(fileId=file_id).execute()
print(response)
if __name__ == '__main__':
file_name = 'Data.docx'
file_id = create_file(file_name)
insert_data(file_id)
download_as_docx(file_id)
download_as_pdf(file_id, file_name)
delete_gdrive_file(file_id)
Error:
returned "Invalid requests[0].insertTable: Index 4 must be less than the end index of the referenced segment, 2.". Details: "Invalid requests[0].insertTable: Index 4
must be less than the end index of the referenced segment, 2.">
I guess end index of the table goes to 67 but even if I try to insert new data at index 68, it either appends in the last cell of the table or it throws indexing error sometimes. I should I make the whole data insertion flow dynamic in the google docs.
In this case, how about the following modification?
Please modify insert_table_data as follows.
def insert_table_data(file_id, values, index):
documentId = file_id
resource = {
"oauth2": credentials,
"documentId": documentId,
"rows": len(values),
"columns": len(values[0]),
# "append": True,
"createIndex": index,
"values": values,
}
gdoctableapp.CreateTable(resource)
And also, please modify insert_data as follows.
def insert_data(file_id):
# Insert texts and images.
index = 1
requests = []
requests.append(insert_image(index))
index += 1
text1 = '\ntext\n'
requests.append(insert_text(index, text1))
index += len(text1)
table1 = index
text2 = '\nDemo text\n'
requests.append(insert_text(index, text2))
index += len(text2)
table2 = index
text3 = '\n'
requests.append(insert_text(index, text3))
index += len(text3)
requests.append(insert_image(index))
docs.documents().batchUpdate(documentId=file_id, body={'requests': requests}).execute()
# Create tables.
values2 = [['Country', 'India'], ['State', 'UP']]
insert_table_data(file_id, values2, table2)
values1 = [['Name of the Client/Organization', 'XYZ'], ['Industry', 'Software']]
insert_table_data(file_id, values1, table1)
# Remove borders of tables.
resource = {"oauth2": credentials, "documentId": file_id}
res = gdoctableapp.GetTables(resource)
obj = {"color": {"color": {}}, "dashStyle": "SOLID", "width": {"magnitude": 0, "unit": "PT"}}
reqs = []
for e in res['tables']:
data = {
"updateTableCellStyle": {
"tableCellStyle": {
"borderBottom": obj,
"borderTop": obj,
"borderLeft": obj,
"borderRight": obj,
},
"tableStartLocation": {
"index": e['tablePosition']['startIndex']
},
"fields": "borderBottom,borderTop,borderLeft,borderRight"
}
}
reqs.append(data)
docs.documents().batchUpdate(documentId=file_id, body={'requests': reqs}).execute()