Hi there! How are you, how is it going? Today I’m going to start a bit differently than my usual blog articles. Right at the beginning of writing this article, a thought came to my mind: I am writing all these articles but I didn’t even get the chance to introduce myself! So let me do just that in a few words before getting to the actual topic.
1.
About Me
My name is Marius, I am 25 years old and I am married to my wonderful wife, Veronica. Together we have one daughter. Her name is Ioana and she is 10 months old. She is a cutie, very curious, and likes exploring the outside world. As a family, we like to go outside for a walk, to discover new places and to spend a good time together. Professionally, I am working as a full stack developer at Control F5 since the beginning of 2022.
2.
Background Story
In our previous article I wrote about Making fillable documents using Google Docs API but what if you have a more complex use case? There are times when replacing placeholders in a copied document is not enough. For example, If you ever wanted to update an existing placeholder, you would have to generate a whole new Google document to achieve that. Maybe you have an application where you constantly need to update/refill document placeholders. In this case, generating a new document every time is costly and inefficient. Therefore let’s check another solution from the Google Docs API documentation, which is Working with named ranges.
3.
The Concept
The idea behind named ranges is pretty simple. A named range is a reference which, in our case, will point to a placeholder in our Google document. A named range can contain one or more ranges since the placeholder can occur more than once in our document. A range contains information about the position where the placeholder begins and ends.
The named range can be used to fill the placeholder with any content you like, as many times as it is needed.
4.
Initializing Named Ranges
In order to create named ranges for our placeholders, we will have to:
- Prepare the Google document body
- Extract the placeholders from the body
- Create and send the named ranges requests to the Google Docs api
5.
Preparing The Data
A Google document body looks like this:
Let’s create a service to gather all the paragraph elements.
<?php
namespace App\Services\NamedRanges;
class DocumentContentsService
{
/**
* @param mixed $documentContents
* @return array
*/
public static function gather(mixed $documentContents): array
{
$contents = [];
self::gatherDocumentContents($documentContents, $contents);
return $contents;
}
/**
* @param array|object $contents
* @param array $results
* @param mixed $parentContent
* @return void
*/
private static function gatherDocumentContents(mixed $contents, array &$results, mixed $parentContent = []): void
{
foreach ($contents as $key => $content) {
if ($key === 'content' && is_string($content)) {
$results[] = [
'text' => $content,
'obj' => $parentContent
];
} elseif (is_object($content) || is_array($content)) {
self::gatherDocumentContents($content, $results, $contents);
}
}
}
}
If we passed the google document body from above through the DocumentContentsService this is the result:
Now, we have all the document contents in an ordered sequence. Therefore I am going to convert the array to a doubly linked list.
This is how I implemented it:
<?php
namespace App\DataStructures;
class Node
{
public ?Node $parent;
public ?Node $child;
public $data;
/**
* @param $data
* @param Node|null $parent
* @param Node|null $child
*/
public function __construct(?Node $parent = null, ?Node $child = null, $data = null)
{
$this->parent = $parent;
$this->child = $child;
$this->data = $data;
}
}
<?php
namespace App\DataStructures;
class DoublyLinkedList
{
public Node $parent;
/**
* @param Node $parent
*/
public function __construct(Node $parent)
{
$this->parent = $parent;
}
/**
* @param array $items
* @return DoublyLinkedList
*/
public static function convert(array $items): DoublyLinkedList
{
$isInitializing = true;
$parent = new Node();
$doubleLinkedList = new DoublyLinkedList($parent);
foreach ($items as $item) {
if ($isInitializing) {
$parent->data = $item;
$isInitializing = false;
} else {
$child = new Node();
$child->data = $item;
$parent->child = $child;
$child->parent = $parent;
$parent = $child;
}
}
return $doubleLinkedList;
}
}
Extracting The Placeholders
The concept is pretty simple:
- Extract all placeholders from the current text node
- Extract the possible placeholder at the end of the current text node
- Store all placeholders in a variable
- Now, let’s write the code. I created a service for this:
<?php
namespace App\Services\NamedRanges;
use App\Actions\NamedRanges\InitializeNamedRanges;
use Google\Exception;
use Google\Service\Docs\BatchUpdateDocumentResponse;
class NamedRangesService
{
/**
* @param string $driveFileId
* @return BatchUpdateDocumentResponse|null
* @throws Exception
*/
public function initNamedRanges(string $driveFileId): ?BatchUpdateDocumentResponse
{
$action = new InitializeNamedRanges();
return $action->execute($driveFileId);
}
}
This service is going to handle the initialization / filling of named ranges.
Where after I created the InitializeNamedRanges action class:
<?php
namespace App\Actions\NamedRanges;
use App\Actions\NamedRanges\Components\InitializeNamedRangesPlaceholder;
use App\Services\GoogleDocsService;
use Google\Exception;
use Google\Service\Docs;
use Google\Service\Docs\BatchUpdateDocumentResponse;
use Google\Service\Docs\Color;
use Google\Service\Docs\CreateNamedRangeRequest;
use Google\Service\Docs\Document;
use Google\Service\Docs\OptionalColor;
use Google\Service\Docs\Range;
use Google\Service\Docs\Request;
use Google\Service\Docs\RgbColor;
use Google\Service\Docs\TextStyle;
use Google\Service\Docs\UpdateTextStyleRequest;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Illuminate\Support\Str;
class InitializeNamedRanges
{
private GoogleDocsService $googleDocsService;
public function __construct()
{
$this->googleDocsService = new GoogleDocsService();
}
/**
* @param string $documentId
* @return BatchUpdateDocumentResponse|null
* @throws Exception
*/
public function execute(string $documentId): ?BatchUpdateDocumentResponse
{
$document = $this->getDocument($documentId);
$action = new ScanForPlaceholdersInGoogleDoc();
$placeholders = $action->execute($document);
$placeholderCollections = collect($placeholders)->groupBy('placeholder');
$requests = $this->createNamedRangesRequests($placeholderCollections);
if ($requests->isNotEmpty()) {
return $this->googleDocsService->batchUpdateDoc($document->documentId, $requests->toArray());
}
return null;
}
/**
* @param string $documentId
* @return Document
* @throws Exception
*/
public function getDocument(string $documentId): Document
{
$docs = new Docs($this->googleDocsService->authorizeClient());
return $docs->documents->get($documentId, [
'fields' => 'documentId,body'
]);
}
/**
* @param Collection $placeholderCollections
* @return Collection
*/
private function createNamedRangesRequests(Collection $placeholderCollections): Collection
{
$requests = collect();
$placeholderCollections->each(function (Collection $placeholderCollection) use ($requests) {
$placeholderCollection->each(function (InitializeNamedRangesPlaceholder $placeholder) use ($requests) {
$this->setPlaceholderRange($placeholder);
$namedRangeRequest = $this->createNamedRangeRequest($placeholder);
$requests->add($namedRangeRequest);
$backgroundColorRequest = $this->createBackgroundColorRequest($placeholder);
$requests->add($backgroundColorRequest);
});
});
$requests->transform(function (Request $request) {
return [
...json_decode(json_encode($request->toSimpleObject()), true)
];
});
return $requests;
}
/**
* @param InitializeNamedRangesPlaceholder $placeholder
* @return void
*/
private function setPlaceholderRange(InitializeNamedRangesPlaceholder $placeholder): void
{
$obj = Arr::first($placeholder->paragraphElements)['obj'];
$startIndex = $obj->newStartIndex;
$endIndex = $startIndex + Str::length($placeholder->placeholder);
$range = new Range();
$range->setStartIndex($startIndex);
$range->setEndIndex($endIndex);
$placeholder->setRange($range);
}
/**
* @param InitializeNamedRangesPlaceholder $placeholder
* @return Request
*/
private function createNamedRangeRequest(InitializeNamedRangesPlaceholder $placeholder): Request
{
$namedRange = new CreateNamedRangeRequest();
$namedRange->setName($placeholder->key);
$namedRange->setRange($placeholder->getRange());
$request = new Request();
$request->setCreateNamedRange($namedRange);
return $request;
}
/**
* @param InitializeNamedRangesPlaceholder $placeholder
* @return Request
*/
private function createBackgroundColorRequest(InitializeNamedRangesPlaceholder $placeholder): Request
{
$rgbColor = new RgbColor();
$rgbColor->setRed(1);
$rgbColor->setBlue(0);
$rgbColor->setGreen(0);
$color = new Color();
$color->setRgbColor($rgbColor);
$optionalColor = new OptionalColor();
$optionalColor->setColor($color);
$textStyle = new TextStyle();
$textStyle->setBackgroundColor($optionalColor);
$updateTextStyleRequest = new UpdateTextStyleRequest();
$updateTextStyleRequest->setRange($placeholder->getRange());
$updateTextStyleRequest->setFields('backgroundColor');
$updateTextStyleRequest->setTextStyle($textStyle);
$request = new Request();
$request->setUpdateTextStyle($updateTextStyleRequest);
return $request;
}
}
This are the responsibilities of the InitializeNamedRanges action class:
- Gather the Google document placeholders
- Prepare the named ranges requests
- Update the Google document by sending a batch update request with the named ranges requests
The createBackgroundColorRequest method is only for debugging purposes.
Let’s not forget the component class used for
creating CreateNamedRangeRequest’s :
<?php
namespace App\Actions\NamedRanges\Components;
use Google\Service\Docs\Range;
class InitializeNamedRangesPlaceholder
{
public string $placeholder;
public string $key;
public array $paragraphElements;
protected Range $range;
/**
* @param string $placeholder
* @param array $paragraphElements
*/
public function __construct(string $placeholder, array $paragraphElements)
{
$this->placeholder = $placeholder;
$this->key = trim($placeholder, '{}');
$this->paragraphElements = $paragraphElements;
}
/**
* @param string $placeholder
* @param array $parts
* @return static
*/
public static function make(string $placeholder, array $parts): static
{
return new static($placeholder, $parts);
}
/**
* @return Range
*/
public function getRange(): Range
{
return $this->range;
}
/**
* @param Range $range
* @return void
*/
public function setRange(Range $range): void
{
$this->range = $range;
}
}
In order to gather the Google document placeholders I have created a dedicated action class called ScanForPlaceholdersInGoogleDoc.
<?php
namespace App\Actions\NamedRanges;
use App\Actions\NamedRanges\Components\InitializeNamedRangesPlaceholder;
use App\DataStructures\DoublyLinkedList;
use App\DataStructures\Node;
use App\Services\NamedRanges\DocumentContentsService;
use Google\Service\Docs\Document;
use Illuminate\Support\Collection;
class ScanForPlaceholdersInGoogleDoc
{
/**
* @param Document $document
* @return Collection
*/
public function execute(Document $document): Collection
{
$body = $document->getBody()->toSimpleObject()->content;
$contents = DocumentContentsService::gather($body);
$doubleLinkedList = DoublyLinkedList::convert($contents);
return $this->findPlaceholders($doubleLinkedList);
}
/**
* @param DoublyLinkedList $doubleLinkedList
* @return Collection
*/
private function findPlaceholders(DoublyLinkedList $doubleLinkedList): Collection
{
$placeholders = collect();
$node = $doubleLinkedList->parent;
$findAllPlaceholdersInText = FindAllPlaceholdersInText::make($this->getCompletePlaceholderPattern());
$findPlaceholderAtEndOfText = FindPlaceholderAtEndOfText::make(
placeholderBeginPattern: $this->getPlaceholderBeginPattern(),
completePlaceholderPattern: $this->getCompletePlaceholderPattern()
);
while ($node instanceof Node) {
$placeholders->push(...$findAllPlaceholdersInText->execute($node));
$possiblePlaceholder = $findPlaceholderAtEndOfText->execute($node);
if ($possiblePlaceholder instanceof InitializeNamedRangesPlaceholder) {
$placeholders->add($possiblePlaceholder);
}
$node = $node->child;
}
return $placeholders->keyBy(function (InitializeNamedRangesPlaceholder $placeholder) {
return $placeholder->paragraphElements[0]['obj']->newStartIndex;
});
}
/**
* @return string
*/
private function getCompletePlaceholderPattern(): string
{
return "/{$this->getPlaceholderBeginPattern()}}{2}/";
}
/**
* @return string
*/
private function getPlaceholderBeginPattern(): string
{
$whitespace = ' ';
return "\{{2}[^}$whitespace{?]+";
}
}
In order to find out the document placeholders it has to:
- Gather the Google document contents
- Convert the contents into a DoublyLinkedList
- Iterate through all text nodes of the Google document
- Extract the complete placeholders from the node’s text
- Extract the possible placeholder at the end of the node’s text
For the placeholders extraction I created 2 separate action classes:
<?php
namespace App\Actions\NamedRanges;
use App\Actions\NamedRanges\Components\InitializeNamedRangesPlaceholder;
use App\DataStructures\Node;
use App\Helpers\MbStr;
use Illuminate\Support\Collection;
class FindAllPlaceholdersInText
{
private string $completePlaceholderPattern;
/**
* @param string $completePlaceholderPattern
*/
public function __construct(string $completePlaceholderPattern)
{
$this->completePlaceholderPattern = $completePlaceholderPattern;
}
/**
* @param string $completePlaceholderPattern
* @return static
*/
public static function make(string $completePlaceholderPattern): static
{
return new static($completePlaceholderPattern);
}
/**
* @return string
*/
public function getCompletePlaceholderPattern(): string
{
return $this->completePlaceholderPattern;
}
/**
* @param Node $node
* @return Collection
*/
public function execute(Node $node): Collection
{
$text = $node->data['text'];
$placeholderMatches = $this->pluckPlaceholderMatches($text);
return $this->convertToPlaceholders($placeholderMatches, $node);
}
/**
* @param string $text
* @return Collection
*/
private function pluckPlaceholderMatches(string $text): Collection
{
preg_match_all($this->getCompletePlaceholderPattern(), $text, $sets, PREG_OFFSET_CAPTURE);
$matches = collect($sets)->flatten(1);
$matches = $matches->mapWithKeys(function (array $match) use ($text) {
yield MbStr::convertToRealUtf8Offset($text, $match[1]) => $match[0];
});
return $matches->sortKeys();
}
/**
* @param Collection $placeholderMatches
* @param Node $node
* @return Collection
*/
private function convertToPlaceholders(Collection $placeholderMatches, Node $node): Collection
{
return $placeholderMatches->transform(function (mixed $match, int $textIndex) use ($node) {
$obj = clone $node->data['obj'];
$obj->textIndex = $textIndex;
$obj->newStartIndex = $obj->startIndex + $textIndex;
$paragraphElements[0]['obj'] = $obj;
return InitializeNamedRangesPlaceholder::make($match, $paragraphElements);
});
}
}
<?php
namespace App\Actions\NamedRanges;
use App\Actions\NamedRanges\Components\InitializeNamedRangesPlaceholder as Placeholder;
use App\DataStructures\Node;
use App\Helpers\MbStr;
class FindPlaceholderAtEndOfText
{
private string $placeholderBeginPattern;
private string $completePlaceholderPattern;
public function __construct(string $placeholderBeginPattern, string $completePlaceholderPattern)
{
$this->placeholderBeginPattern = $placeholderBeginPattern;
$this->completePlaceholderPattern = $completePlaceholderPattern;
}
/**
* @param string $placeholderBeginPattern
* @param string $completePlaceholderPattern
* @return static
*/
public static function make(string $placeholderBeginPattern, string $completePlaceholderPattern): static
{
return new static($placeholderBeginPattern, $completePlaceholderPattern);
}
/**
* @return string
*/
public function getPlaceholderBeginPattern(): string
{
return $this->placeholderBeginPattern;
}
/**
* @return string
*/
public function getCompletePlaceholderPattern(): string
{
return $this->completePlaceholderPattern;
}
/**
* @param Node $node
* @return Placeholder|null
*/
public function execute(Node $node): ?Placeholder
{
$text = $node->data['text'];
$possiblePlaceholderMatch = $this->getPossiblePlaceholderBeginMatch($text);
if (empty($possiblePlaceholderMatch)) {
return null;
}
$possiblePlaceholderInfo = $this->getPossiblePlaceholderInfo($text, $node, $possiblePlaceholderMatch);
return $this->findPossiblePlaceholderAtEnd($node, $possiblePlaceholderInfo);
}
/**
* @param string $text
* @return array
*/
private function getPossiblePlaceholderBeginMatch(string $text): array
{
$patterns = [
"/{$this->getPlaceholderBeginPattern()}$/",
'/\{{2}$/',
'/\{$/'
];
foreach ($patterns as $pattern) {
if (preg_match($pattern, $text, $possiblePlaceholderMatch, PREG_OFFSET_CAPTURE)) {
break;
}
}
return $possiblePlaceholderMatch ?? [];
}
/**
* @param string $text
* @param Node $node
* @param array $possiblePlaceholderMatch
* @return array
*/
private function getPossiblePlaceholderInfo(string $text, Node $node, array $possiblePlaceholderMatch): array
{
$byteOffset = $possiblePlaceholderMatch[0][1];
$textIndex = MbStr::convertToRealUtf8Offset($text, $byteOffset);
$obj = $node->data['obj'];
return [
'previousMatchStartIndex' => $textIndex,
'previousMatchEndIndex' => $obj->endIndex - $obj->startIndex,
'remainingText' => MbStr::substr($text, $textIndex)
];
}
/**
* @param Node $node
* @param array $possiblePlaceholderInfo
* @return Placeholder|null
*/
private function findPossiblePlaceholderAtEnd(Node $node, array $possiblePlaceholderInfo): ?Placeholder
{
$text = $possiblePlaceholderInfo['remainingText'];
$child = $node->child;
$paragraphElements[] = $node->data;
if (!($child instanceof Node)) {
return null;
}
while ($child instanceof Node && !preg_match($this->getCompletePlaceholderPattern(), $text)) {
$paragraphElements[] = $child->data;
$text .= $child->data['text'];
$child = $child->child;
}
return $this->findPossiblePlaceholderBetween(
$possiblePlaceholderInfo['previousMatchStartIndex'],
$possiblePlaceholderInfo['previousMatchEndIndex'],
$paragraphElements
);
}
/**
* @param int $startIndex
* @param int $endIndex
* @param array $paragraphElements
* @return Placeholder|null
*/
private function findPossiblePlaceholderBetween(
int $startIndex,
int $endIndex,
array $paragraphElements
): ?Placeholder
{
$text = collect($paragraphElements)->pluck('text')->implode('');
$possiblePlaceholderMatch = $this->getPossiblePlaceholderMatch($text, $startIndex);
if (empty($possiblePlaceholderMatch)) {
return null;
}
return $this->extractPossiblePlaceholderBetween(
$startIndex,
$endIndex,
$text,
$possiblePlaceholderMatch,
$paragraphElements
);
}
/**
* @param string $text
* @param int $offset
* @return array
*/
private function getPossiblePlaceholderMatch(string $text, int $offset): array
{
preg_match(
pattern: $this->getCompletePlaceholderPattern(),
subject: $text,
matches: $possiblePlaceholderMatch,
flags: PREG_OFFSET_CAPTURE,
offset: $offset
);
return $possiblePlaceholderMatch;
}
/**
* @param array $placeholderMatch
* @param string $text
* @param int $startIndex
* @param int $endIndex
* @param array $paragraphElements
* @return Placeholder|null
*/
private function extractPossiblePlaceholderBetween(
int $startIndex,
int $endIndex,
string $text,
array $placeholderMatch,
array $paragraphElements
): ?Placeholder
{
/**
* @var string $placeholder
* @var int $byteOffset
*/
[$placeholder, $byteOffset] = $placeholderMatch[0];
$textIndex = MbStr::convertToRealUtf8Offset($text, $byteOffset);
if ($textIndex >= $startIndex && $textIndex <= $endIndex) {
$obj = clone $paragraphElements[0]['obj'];
$obj->textIndex = $textIndex;
$obj->newStartIndex = $obj->startIndex + $textIndex;
$paragraphElements[0]['obj'] = $obj;
return Placeholder::make($placeholder, $paragraphElements);
}
return null;
}
}
The FindAllPlaceholdersInText action class is pretty simple.
It extracts all the placeholders from the text using a regex pattern.
The FindPlaceholderAtEndOfText extracts the possible placeholder at the end of a node’s text. Then it scans the next text nodes until a complete placeholder match is found. If the index of that match is between the start- and end index of the first node’s text, this means that our assumption was correct and we can safely add the placeholder to our $placeholders collection.
Finally the helper class converts the byte offset of the preg_match* functions to the real utf8 offset:
<?php
namespace App\Helpers;
use Illuminate\Support\Str;
class MbStr extends Str
{
/**
* @param string $text
* @param int $byteOffset captured by preg_match* functions
* @return int
*/
public static function convertToRealUtf8Offset(string $text, int $byteOffset): int
{
$substring = substr($text, 0, $byteOffset);
return Str::length($substring, 'UTF-8');
}
}
Filling Named Ranges
This is a short summary about how to fill named ranges based on the Google docs api documentation:
- Get the named ranges from the Google document
- Retrieve the named ranges for the placeholders you want to update
- Recalculate the named range indexes for the new content
- Prepare named ranges requests for the new content
- Send a batch update request with the named ranges requests
First things first, let’s add a new method to our NamedRangesService:
<?php
namespace App\Services\NamedRanges;
use App\Actions\NamedRanges\FillNamedRanges;
use App\Actions\NamedRanges\InitializeNamedRanges;
use Google\Exception;
use Google\Service\Docs\BatchUpdateDocumentResponse;
use Illuminate\Support\Collection;
class NamedRangesService
{
/**
* @param string $documentId
* @return BatchUpdateDocumentResponse|null
* @throws Exception
*/
public function initNamedRanges(string $documentId): ?BatchUpdateDocumentResponse
{
$action = new InitializeNamedRanges();
return $action->execute($documentId);
}
/**
* @param string $driveFileId
* @param Collection $placeholders
* @return BatchUpdateDocumentResponse|null
* @throws Exception
*/
public function fillNamedRanges(string $driveFileId, Collection $placeholders): ?BatchUpdateDocumentResponse
{
$action = new FillNamedRanges();
return $action->execute($driveFileId, $placeholders);
}
}
The fillNamedRanges method is going to use a FillNamedRanges action class behind the scenes. In FillNamedRanges I translated the Java code snippet into php and adapted it with some functionalities from the Laravel framework:
<?php
namespace App\Actions\NamedRanges;
use App\Actions\NamedRanges\Components\FillNamedRangesPlaceholder;
use App\Services\GoogleDocsService;
use App\Services\NamedRanges\Components\Placeholder;
use Google\Exception as GoogleException;
use Google\Service\Docs;
use Google\Service\Docs\BatchUpdateDocumentResponse;
use Google\Service\Docs\Color;
use Google\Service\Docs\CreateNamedRangeRequest;
use Google\Service\Docs\DeleteContentRangeRequest;
use Google\Service\Docs\Document;
use Google\Service\Docs\InsertTextRequest;
use Google\Service\Docs\Location;
use Google\Service\Docs\NamedRanges;
use Google\Service\Docs\OptionalColor;
use Google\Service\Docs\Range;
use Google\Service\Docs\Request;
use Google\Service\Docs\RgbColor;
use Google\Service\Docs\TextStyle;
use Google\Service\Docs\UpdateTextStyleRequest;
use Illuminate\Support\Collection;
use Illuminate\Support\Str;
class FillNamedRanges
{
private GoogleDocsService $service;
public function __construct()
{
$this->service = new GoogleDocsService();
}
/**
* @param string $driveFileId
* @param Collection $placeholders
* @return BatchUpdateDocumentResponse|null
* @throws GoogleException
*/
public function execute(string $driveFileId, Collection $placeholders): ?BatchUpdateDocumentResponse
{
if ($placeholders->isEmpty()) {
return null;
}
[$allRanges, $insertIndexes] = $this->makeAllRanges($driveFileId, $placeholders);
$requests = $this->makeCreateNamedRangesRequests($allRanges, $insertIndexes);
if ($requests->isNotEmpty()) {
$response = $this->service->batchUpdateDoc($driveFileId, $requests->values()->toArray());
}
return $response ?? null;
}
/**
* @param string $driveFileId
* @param Collection $placeholders
* @return array<int, Collection, Collection>
* @throws GoogleException
*/
private function makeAllRanges(string $driveFileId, Collection $placeholders): array
{
$document = $this->getDocument($driveFileId);
$allRanges = collect();
$insertIndexes = collect();
$placeholders->each(function (Placeholder $placeholder) use ($document, $allRanges, $insertIndexes) {
/**
* @var NamedRanges|null $namedRanges
*/
$namedRanges = $document->getNamedRanges()[$placeholder->key] ?? null;
if ($namedRanges instanceof NamedRanges) {
// disallow edge case for placeholders with no values
if (empty($placeholder->value)) {
return;
}
foreach ($namedRanges->getNamedRanges() as $namedRange) {
foreach ($namedRange->getRanges() as $range) {
$namedRangePlaceholder = $this->makeNamedRangePlaceholder($range, $placeholder);
$insertIndexes->add($range->getStartIndex());
$allRanges->add($namedRangePlaceholder);
break;
}
}
}
});
$allRanges = $allRanges->sortByDesc(function (FillNamedRangesPlaceholder $placeholder) {
return $placeholder->oldRange->getStartIndex();
});
return [$allRanges, $insertIndexes];
}
/**
* @param string $driveFileId
* @return Document
* @throws GoogleException
*/
private function getDocument(string $driveFileId): Document
{
$docs = new Docs($this->service->authorizeClient());
return $docs->documents->get($driveFileId, ['fields' => 'namedRanges']);
}
/**
* @param Range $oldRange
* @param Placeholder $placeholder
* @return FillNamedRangesPlaceholder
*/
private function makeNamedRangePlaceholder(Range $oldRange, Placeholder $placeholder): FillNamedRangesPlaceholder
{
$newRangeStartIndex = $oldRange->getStartIndex();
$newRangeEndIndex = $newRangeStartIndex + Str::length($placeholder->value);
$newRange = new Range();
$newRange->setStartIndex($newRangeStartIndex);
$newRange->setEndIndex($newRangeEndIndex);
return new FillNamedRangesPlaceholder(
$placeholder->key,
$placeholder->value,
$oldRange,
$newRange
);
}
/**
* @param Collection $allRanges
* @param Collection $insertIndexes
* @return Collection
*/
private function makeCreateNamedRangesRequests(Collection $allRanges, Collection $insertIndexes): Collection
{
$requests = collect();
$allRanges->each(function (FillNamedRangesPlaceholder $placeholder) use ($insertIndexes, $requests) {
if (!$insertIndexes->contains($placeholder->oldRange->getStartIndex())) {
return;
}
// Delete all the content in the existing range.
// Insert the replacement text.
// Re-create the named range on the new text.
$deleteContentRangeRequest = $this->makeDeleteContentRangeRequest($placeholder->oldRange);
$insertTextRequest = $this->makeInsertTextRequest($placeholder, $placeholder->oldRange);
$createNamedRangeRequest = $this->makeCreateNamedRangeRequest($placeholder, $placeholder->newRange);
$requests->push(
$deleteContentRangeRequest,
$insertTextRequest,
$createNamedRangeRequest,
);
$backgroundColorRequest = $this->createBackgroundColorRequest($placeholder->newRange);
$requests->add($backgroundColorRequest);
});
$requests->transform(function (Request $request) {
return [
...json_decode(json_encode($request->toSimpleObject()), true)
];
});
return $requests;
}
/**
* @param Range $range
* @return Request
*/
private function makeDeleteContentRangeRequest(Range $range): Request
{
$deleteContentRangeRequest = new DeleteContentRangeRequest();
$deleteContentRangeRequest->setRange($range);
$googleDocsRequest = new Request();
$googleDocsRequest->setDeleteContentRange($deleteContentRangeRequest);
return $googleDocsRequest;
}
/**
* @param FillNamedRangesPlaceholder $placeholder
* @param Range $oldRange
* @return Request
*/
private function makeInsertTextRequest(FillNamedRangesPlaceholder $placeholder, Range $oldRange): Request
{
$location = new Location();
$location->setSegmentId($oldRange->getSegmentId());
$location->setIndex($oldRange->getStartIndex());
$insertTextRequest = new InsertTextRequest();
$insertTextRequest->setLocation($location);
$insertTextRequest->setText($placeholder->value);
$googleDocsRequest = new Request();
$googleDocsRequest->setInsertText($insertTextRequest);
return $googleDocsRequest;
}
/**
* @param FillNamedRangesPlaceholder $placeholder
* @param Range $newRange
* @return Request
*/
private function makeCreateNamedRangeRequest(FillNamedRangesPlaceholder $placeholder, Range $newRange): Request
{
$createNamedRangeRequest = new CreateNamedRangeRequest();
$createNamedRangeRequest->setName($placeholder->key);
$createNamedRangeRequest->setRange($newRange);
$googleDocsRequest = new Request();
$googleDocsRequest->setCreateNamedRange($createNamedRangeRequest);
return $googleDocsRequest;
}
/**
* @param Range $newRange
* @return Request
*/
private function createBackgroundColorRequest(Range $newRange): Request
{
$rgbColor = new RgbColor();
$rgbColor->setRed(0);
$rgbColor->setBlue(1);
$rgbColor->setGreen(0);
$color = new Color();
$color->setRgbColor($rgbColor);
$optionalColor = new OptionalColor();
$optionalColor->setColor($color);
$textStyle = new TextStyle();
$textStyle->setBackgroundColor($optionalColor);
$updateTextStyleRequest = new UpdateTextStyleRequest();
$updateTextStyleRequest->setRange($newRange);
$updateTextStyleRequest->setFields('backgroundColor');
$updateTextStyleRequest->setTextStyle($textStyle);
$request = new Request();
$request->setUpdateTextStyle($updateTextStyleRequest);
return $request;
}
}
And it’s placeholder component:
<?php
namespace App\Actions\NamedRanges\Components;
use App\Services\NamedRanges\Components\Placeholder;
use Google\Service\Docs\Range;
class FillNamedRangesPlaceholder extends Placeholder
{
public Range $oldRange;
public Range $newRange;
/**
* @param string $key
* @param string|int|float $value
* @param Range $oldRange
* @param Range $newRange
*/
public function __construct(string $key, string|int|float $value, Range $oldRange, Range $newRange)
{
parent::__construct($key, (string) $value);
$this->oldRange = $oldRange;
$this->newRange = $newRange;
}
}
FillNamedRangesPlaceholder class extends the Placeholder:
<?php
namespace App\Services\NamedRanges\Components;
class Placeholder
{
public string $key;
public string|int|float|null $value;
/**
* @param string $key
* @param mixed $value
*/
public function __construct(string $key, string|int|float|null $value)
{
$this->key = $key;
$this->value = !is_null($value) ? (string) $value: $value;
}
}
I am going to store this class under AppServicesNamedRangesComponents since it’s not only for the action class. It might also be used somewhere else in the code.
Test of Truth
Now it’s time to test the whole code. I created a small snippet:
$service = new DriveDocumentsService();
$doc = $service->copyGoogleDoc(User::find(2));
$service = new NamedRangesService();
$service->initNamedRanges($doc->file_id);
// breakpoint
$service->fillNamedRanges($doc->file_id, collect([
new Placeholder('email', $doc->user->email),
new Placeholder('name', $doc->user->name),
new Placeholder('now', Carbon::now()->toDateString()),
]));
I added a breakpoint in the code after the named ranges are initialized.
I am going to debug this using xdebugger. This is how initialized named ranges look like:
Awesome! Now let’s step over the fillNamedRanges method:
Now let’s update only the name of our user in the document by using another short snippet:
$service = new NamedRangesService();
$service->fillNamedRanges('1anx4ssn_kmi3KAYDjPcqpzdJv2qpIbq0p25aJQuKUBs', collect([
new Placeholder('name', 'Thomas Jefferson'),
]));
As a test I updated the user name from John Doe to Thomas Jefferson and it worked:
Fantastic! That’s what we wanted. Let’s sum things up.
Things To Keep in Mind
When working with named ranges there are some things to keep in mind:
- The created named ranges are not private. That means that anyone who has access to the Google document can see them.
- Copying the template and initializing named ranges can take some time, therefore it is advised to defer this process somewhere else in your logic.
Conclusion
Named ranges are great for swapping the old content and for filling a Google document gradually. I know that this has been a pretty long article but take a deep breath, enjoy a relaxing cup of tea and take your time with the code. As always, Happy coding!
Useful Links
Working with named ranges | Google Docs
Method: documents.batchUpdate | Google Docs
preg_match and UTF-8 in PHP – Stack Overflow
Educative Links
Doubly linked list – Wikipedia
What are Laravel Action Classes and How to use them? | by Reza Khademi
Laravel “app/Actions” Pattern: Useful or Over-Engineering?
Relational Services
OUR WORK
Case studies
We have helped 20+ companies in industries like Finance, Transportation, Health, Tourism, Events, Education, Sports.
Backend System
Travel