Google Docs API Batch Update - multiple updates to same template file?

Question

I am trying to use the Google Docs API to auto generate a document based on values from a JSON file.

I have created a template Google Doc which is structured like this:

I went to the shop and bought the following item: {{foodtype}}. 

It cost {{price}}.

The JSON looks like this:

{
'food':'apples",
'price':'10'
}

So using the Google Docs replaceAllText method I can use a Python script to update the doc like this:

def update_doc(food_json,docs)
    

 requests=[
        {
            'replaceAllText': {
                'containsText': {
                    'text': '{{foodtype}}',
                    'matchCase': 'true'
                },
                'replaceText': 'food_json['food']',
            }},]

   

 doc_id = 'xxxxxxxxxxxxx'
 result = docs.documents().batchUpdate(documentId=doc_id, body={'requests': requests}).execute()

This works perfectly. The {{foodtype}} tag is replaced in the document with the string from the JSON.

My problem is that I cannot do this when I want to add multiple updates to a document. Sometimes there might be 5, 10 or even 50 items to add so manually adding tags to a template is not possible.

For example if my JSON looked like this:

{
 {basket:
 {'food':'apples','price':'10'},
 {'food':'bread', 'price':'15'}
 {'food':'bananas', 'price': '5'}
 }
etc etc etc
}

I want to be able to write a for loop to iterate through the JSON and write a report for every item in the JSON e.g.:

I went to the shop and bought the following item: apple. 
It cost 10 cents.

I went to the shop and bought the following item: bread.
It cost 15 cents.

I went to the shop and bought the following item: bananas.
It cost 5 cents.

The code would look something like this:

requests = []

for f in food_json['basket']:
   requests.append(f)


requests=[
        {
            'replaceAllText': {
                'containsText': {
                    'text': '{{foodtype}}',
                    'matchCase': 'true'
                },
                'replaceText': 'f['food']',
            }},
            'replaceAllText': {
                'containsText': {
                    'text': '{{price}}',
                    'matchCase': 'true'
                },
                'replaceText': 'f['price']',
            }},

]

*Push the update to the Google docs*

However this fails because on the first iteration of the for loop, the text matching the {{foodtype}} tag is replaced and then there is nothing to replace on the next iteration.

I'm stuck as to how to proceed.

I have previously used MS Word and the awesome python-docx-template library to publish documents in this way. It was possible to add Jinja tags in the Word template and then use a for loop directly in the document to achieve what I wanted e.g. just put something like this in a template:

{% for b in basket %}
I went to the shop and bought:

{{ b.food }}
The price was:

{{ b.price }}
{% endfor %}

This worked perfectly, but I cannot see how to reproduce the same result with the Google Docs API and Python. Any suggestions?

Answer 1

I believe your situation and goal are as follows.

• In your situation, there are several same texts of {{foodtype}} in a Document. And the number of {{foodtype}} is the same as the value of food.
• You want to replace 1st {{foodtype}} with the 1st value of food.
• You want to achieve this using googleapis for python.

If my understanding is correct, unfortunately, in the current stage, when replaceAllText of Docs API is used, all values of {{foodtype}} are replaced with the 1st value of food in one batch request. I thought that this might be the reason for your issue.

Flow:

In order to achieve your goal using Docs API with python, as a workaround, I would like to propose the following flow.

1. Search each {{foodtype}} and {{price}} in the texts of Google Document.
   • In this case, I used documents.get method.
2. Add the count to the texts of {{foodtype}} and {{price}}. It's like {{foodtype1}}, {{price1}}, {{foodtype2}} and {{price2}} and so on.
3. For the texts like {{foodtype1}}, {{price1}}, {{foodtype2}} and {{price2}} and so on, each value is replaced with them using replaceAllText request.

In this flow, the batchUpdate request is used for one call. The sample script is as follows.

Sample script:

# Please set the values you want to replace.
sample = {'basket': [
          {'food': 'apples', 'price': '10'},
          {'food': 'bread', 'price': '15'},
          {'food': 'bananas', 'price': '5'}
          ]}
documentId = '###' # Please set your document ID.


docs = build('docs', 'v1', credentials=creds)
obj = docs.documents().get(documentId=documentId, fields='body').execute()
content = obj.get('body').get('content')
foodCount = 0
priceCount = 0
requests = []
for c in content:
    if 'paragraph' in c:
        p = c.get('paragraph')
        for e in p.get('elements'):
            textRun = e.get('textRun')
            if textRun:
                text = textRun.get('content')
                if '{{foodtype}}' in text:
                    foodCount += 1
                    requests.append(
                        {
                            "replaceAllText": {
                                "replaceText": sample['basket'][foodCount - 1]['food'],
                                "containsText": {
                                    "text": '{{foodtype' + str(foodCount) + '}}',
                                    "matchCase": True
                                }
                            }
                        }
                    )
                    requests.append({
                        "insertText": {
                            "location": {
                                "index": e['startIndex'] + text.find('{{foodtype}}') + len('{{foodtype')
                            },
                            "text": str(foodCount)
                        }})

                if '{{price}}' in text:
                    priceCount += 1
                    requests.append(
                        {
                            "replaceAllText": {
                                "replaceText": sample['basket'][priceCount - 1]['price'],
                                "containsText": {
                                    "text": '{{price' + str(priceCount) + '}}',
                                    "matchCase": True
                                }
                            }
                        }
                    )
                    requests.append({
                        "insertText": {
                            "location": {
                                "index": e['startIndex'] + text.find('{{price}}') + len('{{price')
                            },
                            "text": str(priceCount)
                        }})

if requests != []:
    requests.reverse()
    docs.documents().batchUpdate(documentId=documentId, body={'requests': requests}).execute()

Note:

• In your question, the following values are shown as a sample value.

    {
     {basket:
     {'food':'apples','price':'10'},
     {'food':'bread', 'price':'15'}
     {'food':'bananas', 'price': '5'}
     }
    etc etc etc
    }
  

  • But, I thought that this might not be correct. So in my sample script, I used the following sample value. Please modify each value for your actual situation.

      sample = {'basket': [
                {'food': 'apples', 'price': '10'},
                {'food': 'bread', 'price': '15'},
                {'food': 'bananas', 'price': '5'}
                ]}
    

• In this sample script, as the sample for explaining my workaround, the paragraphs in Google Document are searched. For example, when you want to search the texts in the tables, please modify the above script.

• This sample script supposes that you have already been able to get and put values for Google Document using Docs API. Please be careful this.

References:

• Method: documents.get
• Method: documents.batchUpdate
• ReplaceAllTextRequest