Creating a Google Doc through the REST API … from Salesforce APEX

After writing a first version of Salesforce Flow Action Component that can easily create a Google Spreadsheet and export any sObject data to it (You can find it here on GitHub) I was emboldened to also take on creating a Google Doc straight from a Salesforce FLOW. This proved much harder — mainly because compared to spreadsheets, Docs have a way more ‘loose’ structure, AND the REST API for Docs proved way less productive than then Google Sheet API (Is it a coincidence that the Google Sheet API is at version 4 while the Google Docs api is still V1?).

In my use case I had what I considered a limited need:

• Always creating (or erasing) a new document from scratch is Ok — I am not looking for ways to update / extend existing documents
• I need to do paragraph formatting (Header 1/2/3 etc) and text formatting (bold, italic, bullet) (which in my case is ok to apply to a whole paragraph only)
• I need to create tables, especially the 2 column one that has not borders and has a bold title in the left cell that is about 25% of the table width and has a paragraph of text in the other cell.

Just coming out of the creation of the Apex Flow component to transfer sObject data straight into a Google Spreadsheet I decided to start with the table functionality.

Creating (and filling) Tables in Google Docs through the REST API

After quite a bit of research and trial and error it looks like the only way to add a table with data inside in a Google Doc is to first create the (whole) table and then fill each cell and apply formatting to the table to (in my case remove all borders)

All these steps are one or more requests that are batched together in a BatchUpdate.

So before getting into the nitty gritty of adding a table, the ONE thing that proved easy in Google docs is to add text at the end of the document:

// Add a paragraph of text to the document
String body ='{"requests": [{"insertText": { "text": "'+text+'", "endOfSegmentLocation": {} }} ]}';
HttpResponse responseHttp = executeCallout('POST','callout:'+callOut+'/v1/documents/'+gDocs+':batchUpdate',body);
boolean ret = (responseHttp.getStatusCode()==200);

Notice the “endOfSegmentLocation” being empty, which means ‘end of document’. Easy! If you want to insert text at the beginning of the document, use “location”: {“index”:1} instead (and omit “endOfSegmentLocation” completely. Now if you wanted to insert text in a specific spot — like inside a specific cell of a table you will need to know the index! And another major disappointment — you can only insert plain, unformatted text. So if you want to use bold or italic — you’ll have to again specific the exact index (positions) to add that formatting. Frustrating!

So lets go through the requests need to create table with content.

First we’ll do a InsertTableRequest, which is very straight forward as well, again with that option to either use a specific location or end of document.

String body ='{"requests": [{"insertTable": { "rows": '+String.valueOf(rows)+', "columns": '+String.valueOf(cols)+', "location": {"index":1} }},'+json+']}';

This is for all intends and purposes you’ll want to create a Table ONLY at the beginning of the document. Because ONLY then will you know the index location of the table and therefor can also deduct the index locations of the cells that are created so that you can add content to the table in the same request. I find it a huge miss that a) you cannot add table content in the InsertTableRequest or b) at least formatting information (borders?).

Now that the table is created at Index 1 position there is logic to the index positions of the (empty) cells. This is what I figured out:

the index positions to insert content in a table that was created at position 1

Now we can add InsertText requests (one per cell yes!) — to fill the table. But beware ‘Index’ is literally the ‘cursor’ index in the text so everytime sometime gets added the index of the items after that insert changes. So the only way to add content to the above empty table is to work from the bottom right to top left. When we add text to Index 29 new text is added, but index 27 is still the empty cell position next to it!

So my loop to create those request looks something like this:

  for (row=rows-1; row > -1; row-- ) {
     obj =allObjects[row];
     // for each record in the input list, add a row in the table
     String field;
     Map<String, Object> fieldsAndValues = obj.getPopulatedFieldsAsMap();            
     sObject child;
     Object value ;
     String stringValue;
     For (Integer i = cols-1; i > -1; i-- ){
         field =fieldList[i];
         if (json.length()>0) {
            json+=',';
         }    
         try {    
            if (field.contains('__r')){
              child = (sobject) fieldsAndValues.get(field.substringBefore('.'));
              value = child.get(field.substringAfter('.'));
            } else {
              value = fieldsAndValues.get(field);
            }
            stringValue = stringify(value,columnTypes[i],field);  
          } catch (Exception e) {
            stringValue = field+'- not found!';      
        }          
        json += '{ "insertText": { "text": "'+stringValue+'", "location": { "index": '+String.valueOf( row*((2*cols)+1)+5+2*i   )+' }}}';    
    }          
 }

The resulting json string is added right behind the InsertTableRequest:

   String body ='{"requests": [{"insertTable": { "rows": '+String.valueOf(rows)+', "columns": '+String.valueOf(cols)+', "location": {"index":1} }},'+json+']}';  
   HttpResponse responseHttp = executeCallout('POST','callout:'+callOut+'/v1/documents/'+gDocs+':batchUpdate',body);

The default table has solid lines (as in the example picture above). So for my requirements I also need to be able to add a table that has no borders. That can be done with one bizarrely long request. We need to set the border with to 0, while still telling it that the dashstyle=SOLID and we also still need to define a color (!). So this is the single request to set the borders to invisible for a table.

{
  "updateTableCellStyle": {
    "tableCellStyle": {
      "borderTop": {
        "dashStyle": "SOLID",
        "width": {
          "unit": "PT",
          "magnitude": 0
        },
        "color": {
          "color": {
            "rgbColor": {
              "blue": 0
            }
          }
        }
      },
      "borderBottom": {
        "dashStyle": "SOLID",
        "width": {
          "magnitude": 0,
          "unit": "PT"
        },
        "color": {
          "color": {
            "rgbColor": {
              "blue": 0
            }
          }
        }
      },
      "borderLeft": {
        "dashStyle": "SOLID",
        "width": {
          "unit": "PT",
          "magnitude": 0
        },
        "color": {
          "color": {
            "rgbColor": {
              "blue": 0
            }
          }
        }
      },
      "borderRight": {
        "dashStyle": "SOLID",
        "width": {
          "unit": "PT",
          "magnitude": 0
        },
        "color": {
          "color": {
            "rgbColor": {
              "blue": 0
            }
          }
        }
      }
    },
    "fields": "borderTop, borderBottom, borderLeft, borderRight",
    "tableStartLocation": {
      "index": 2
    }
  }
}

Seems crazy, right? Thought so too :) Oh and notice that we’re using the ‘tableStartLocation’ here, since we know where it is (we inserted it at location 1, so apparently 2 is now its location.

I’m still working on formatting (bold etc), so I’ll post about that in a new blog post, hopefully also with the full code on GitHub. Right now you can already download / install the Apex Flow to Google Sheet module.