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()

Wow! I can't believe I spent DAYS reading all of the documentation for the API and there was absolutely no mention of any of this!

I must have read 50 things on various forums and there wasn't a single one that explained it correctly.

Then, I took the script at the top of this post and converted the structure into python code and it worked!

But then, I saw @arthur-b-renaud 's post and WOW!!!!!!!!!!!!!!!!!!! This thing is insane! Not only did it work, but when I added more text and tried to figure out how to change the dimensions of the table, I realized that it's all automated!

Arthur, you have no idea how much this helps! I really REALLY appreciate it.

Thank you

Although I'm not sure whether those are useful, in order to manage tables on Google Document using Google Docs API, in the current stage, 4 kinds of libraries are existing. https://github.com/tanaikech/gdoctableapppy?tab=readme-ov-file#languages