Creating New Table and Putting Values to Cells using Google Docs API with Google Apps Script

submit.md

Creating New Table and Putting Values to Cells using Google Docs API with Google Apps Script

This is a sample script for creating new table and putting values to cells using Google Docs API with Google Apps Script. Unfortunately, in the current stage, although I had been looking for the method for creating a table and putting the values in each cell at the official document, I couldn't find. Google Docs API is growing now. So such documents might be not prepared yet. By this situation, I investigated about the method for achieving this method.

Here, I would like to introduce a sample script for creating a table and putting the values in each cell using Google Docs API.

I think that this method can be also used for the case that the Docs API is used with other languages except for Google Apps Script.

Flow:

The flow of this sample script is as follows.

1. Create a table with 2 rows and 2 columns.
2. Put a text of A1, B1, A2 and B2 to the cells "A1:B2" of the table.

In this sample script, above flow can be achieved by one API call.

Sample script:

When you use this script, please enable Google Docs API at Advanced Google Services.

function myFunction() {
  var doc = DocumentApp.getActiveDocument();
  var resource = {
    requests: [
      { insertTable: { rows: 2, columns: 2, location: { index: 1 } } },
      { insertText: { text: "B2", location: { index: 12 } } },
      { insertText: { text: "A2", location: { index: 10 } } },
      { insertText: { text: "B1", location: { index: 7 } } },
      { insertText: { text: "A1", location: { index: 5 } } }
    ]
  };
  Docs.Documents.batchUpdate(resource, doc.getId());
}

Result:

This table including values could be created by one API call.

IMPORTANT:

• From this investigation, the following results were obtained.

  • For the row, the index is required to set every 5 index.
  • For the column, the index is required to set every 2 index.

• As an important point, when the values are put in cells, please put in order of "B2", "A2", "B1" and "A1". Because when "A1" is firstly put, the indexes for other cells are changed.

Note:

• These sample scripts use the scope of https://www.googleapis.com/auth/documents.
• Google Docs API is growing now. So I think that in the future, more simple method for this situation might be added.

References:

• Advanced Google Services
• InsertTableRequest
• Insert table in Google Docs API Python
  • This method was also used for this thread.

This is cool... just wish there was a guide on how to insert rows (with let's say 2-3 columns and fill those rows up with data). This whole Google Docs API is sooo confusing.

Keeping track of the index is the hardest part. The API inserts some extra characters you need to keep track of. Increment the index :

• index +=1 for the start of the table
• index +=1 for the start of each row
• index +=2 for the start of each cell
• index +=text.length in each cell
• index +=2 for the end of the table

The documentation wasn't sufficient on this (see https://developers.google.com/docs/api/reference/rest/v1/documents/request#inserttablerequest )

To whom it might be useful, in dummy cases, the following works for me:
`

def add_table_at_the_end_of_google_doc(google_creds, document_id, table_as_list_of_tuples: List[Tuple]):
"""
table_as_list_of_tuples = [('Coucou', 'Coucou2'), ('Coucou3', 'Coucou4'), ('Coucou31', 'Coucou41')]
"""
# 1 : insert a table at the end of the file
requests = [{
'insertTable': {
'rows': len(table_as_list_of_tuples),
'columns': len(table_as_list_of_tuples[0]),
'endOfSegmentLocation': {
'segmentId': ''
}
},
}
]
docs = build('docs', 'v1', credentials=google_creds)
docs.documents().batchUpdate(documentId=document_id, body={'requests': requests}).execute()

content = docs.documents().get(documentId=document_id, fields='body').execute().get('body').get('content')
tables = [c for c in content if c.get('table')]
last_table_start_index = tables[-1]['startIndex']

# Building requests
requests_l, index = [], last_table_start_index
for i_row, row in enumerate(table_as_list_of_tuples):
    index += 1
    for i_cell, cell in enumerate(row):
        index += 2
        requests_l.append(
            {
                'insertText': {
                    'location': {
                        'index': index
                    },
                    'text': cell
                }
            }
        )
requests_l.reverse()
result = docs.documents().batchUpdate(documentId=document_id, body={'requests': requests_l}).execute()

`

For anyone whom this would help, this works in basic cases.

def add_table_at_the_end_of_google_doc(google_creds, document_id, table_as_list_of_tuples: List[Tuple]):
    """
    table_as_list_of_tuples = [('Coucou', 'Coucou2'), ('Coucou3', 'Coucou4'), ('Coucou31', 'Coucou41')]
    """
    # 1 : insert a table at the end of the file
    requests = [{
        'insertTable': {
            'rows': len(table_as_list_of_tuples),
            'columns': len(table_as_list_of_tuples[0]),
            'endOfSegmentLocation': {
                'segmentId': ''
            }
        },
    }
    ]
    docs = build('docs', 'v1', credentials=google_creds)
    docs.documents().batchUpdate(documentId=document_id, body={'requests': requests}).execute()

    content = docs.documents().get(documentId=document_id, fields='body').execute().get('body').get('content')
    tables = [c for c in content if c.get('table')]
    last_table_start_index = tables[-1]['startIndex']

    # Building requests
    requests_l, index = [], last_table_start_index
    for i_row, row in enumerate(table_as_list_of_tuples):
        index += 1
        for i_cell, cell in enumerate(row):
            index += 2
            requests_l.append(
                {
                    'insertText': {
                        'location': {
                            'index': index
                        },
                        'text': cell
                    }
                }
            )
    requests_l.reverse()
    result = docs.documents().batchUpdate(documentId=document_id, body={'requests': requests_l}).execute()