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

GasTips
Google Apps Script / Document / Docs API / table

Gists

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:

 Share!