Google Docs API for creating invoice containing table of variable number of rows

Question

I have a template file for my invoice with a table with sample row, but I want to add more rows dynamically based on a given array size, and write the cell values from the array...

Template's photo

I've been struggling for almost 3 days now.

Is there any easy way to accomplish that? Here's the template file: Link to the Docs file(template)

And here's a few sample arrays of input data to be replaced in the Template file:

[
    [
        "Sample item 1s",
        "Sample Quantity 1",
        "Sample price 1",
        "Sample total 1"
    ],
    [
        "Sample item 2",
        "Sample Quantity 2",
        "Sample price 2",
        "Sample total 2"
    ],
    [
        "Sample item 3",
        "Sample Quantity 3",
        "Sample price 3",
        "Sample total 3"
    ],
]

Now, the length of the parent array can vary depending on the number of items in the invoice, and that's the only problem that I'm struggling with.

And... Yeah, this is a duplicate question, I've found another question on the same topic, but looking at the answers and comments, everyone is commenting that they don't understand the question whereas it looks perfectly clear for me.

Google Docs Invoice template with dynamically items row from Google Sheets

I think the person who asked the question have already quit from it. :(

By the way I am using the API for PHP (Google API Client Library for PHP), and code for replacing dummy text a Google Docs Document by the actual data is given below:

public function replaceTexts(array $replacements, string $document_id) {
    # code...
    $req = new Docs\BatchUpdateDocumentRequest();
    // var_dump($replacements);
    // die();
    foreach ($replacements as $replacement) {
        $target = new Docs\SubstringMatchCriteria();
        $target->text = "{{" . $replacement["targetText"] . "}}";
        $target->setMatchCase(false);
        $req->setRequests([
            ...$req->getRequests(),
            new Docs\Request([
                "replaceAllText" => [
                    "replaceText" => $replacement["newText"],
                    "containsText" => $target
                ]
            ]),
        ]);
    }

    return $this->docs_service->documents->batchUpdate(
        $document_id,
        $req
    );
}

Answer 1

A possible solution would be the following

1. First prep the document by removing every row from the table apart from the title.

2. Get the full document tree from the Google Docs API. This would be a simple call with the document id

   $doc = $service->documents->get($documentId);

3. Traverse the document object returned to get to the table and then find the location of the right cell. This could be done by looping through the elements in the body object until one with the right table field is found. Note that this may not necessarily be the first one since in your template, the section with the {{CustomerName}} placeholder is also a table. So you may have to find a table that has the first cell with a text value of "Item".

4. Add a new row to the table. This is done by creating a request with the shape:

   [ 'insertTableRow' => [ 'tableCellLocation' => [ 'rowIndex' => 1, 'columnIndex' => 1, 'tableStartLocation' => [ 'index' => 177 ] ] ] ]

The tableStartLocation->index element is the paragraph index of the cell to be entered, i.e. body->content[i]->table->startIndex. Send the request.

5. Repeat steps 2 and 3 to get the updated $doc object, and then access the newly created cell i.e. body->content[i]->table->tableRows[j]->tableCells[k]->content->paragraph->elements[l]->startIndex.

6. Send a request to update the text content of the cell at the location of the startIndex from 5 above, i.e.

   [ 'insertText' => [ 'location' => [
   'index' => 206, ] ], 'text' => 'item_1' ] ]

7. Repeat step 5 but access the next cell. Note that after each update you need to fetch an updated version of the document object because the indexes change after inserts.

To be honest, this approach is pretty cumbersome, and it's probably more efficient to insert all the data into a spreadsheet and then embed the spreadsheet into your word document. Information on that can be found here How to insert an embedded sheet via Google Docs API?.

As a final note, I created a copy of your template and used the "Try this method" feature in the API documentation to validate my approach so some of the PHP syntax may be a bit off, but I hope you get the general idea.