Pushing Data Into Google Sheets Sheets (Yes, Multiples)

If you recall from the last tech post about pushing data directly into Google Sheets, I promised a follow-up regarding the process of adding multiple sheets worth of data to a given parent sheet.

Now, I apologize in advance but the headline image I snipped is totally a screenshot of Excel, not Google Sheets, but I was lazy and writing the posts at the same time, so I just used the same example with multiple tabs. The principle of the interface is the same, even if the image represents a different product.

The Need

As I started pushing data into Google Sheets, there were a few projects/datasets that had historically (and manually) bolted together into one parent Excel workbook, thus multiple "sheets" or "tabs" of data. Out of the box, my previous two examples of creating files directly and updating data in said files assumes n==1 sheet, and only 1 sheet (the default).

I needed to find a proper solution to both automatically bolt the datasets together and ship it to Shared Drives. This was, at first, far from trivial to figure out. Sometimes when using the PHP API library, figuring out the nuances of some of the REST calls (since you're not making them directly) can be a challenge. Hence the desire to eventually blog them out.

Enter: Batch Requests

Ultimately, I ended up creating one intermediary step after the initial file creation ($service->files->create) and before the file read/update request (e.g. $sheets->spreadsheets_values->update). This step creates the requisite "sheets" or "tabs" before we read and load data into them, allowing us some control over their indexes (tab order) and names, like so (simple example for a two-file/sheet/tab upload):

// CREATE AND UPDATE ADEQUATE NUMBER OF DESTINATION SHEET TABS
$sheetNameAndRange0 = basename($localFile1, '.csv');
$sheetNameAndRange1 = basename($localFile2, '.csv');
$updateRequests = [
  new Google_Service_Sheets_Request([
    'updateSheetProperties' => [
      'properties' => [
        'title' => $sheetNameAndRange0,
        'index' => 0
      ],
      'fields' => 'title'
    ]
  ]),
  new Google_Service_Sheets_Request([
    'addSheet' => [
      'properties' => [
        'title' => $sheetNameAndRange1,
        'index' => 1
      ]
    ]
  ])
];

// RUN THE UPDATE REQUESTS
$batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
    'requests' => $updateRequests
]);
$sheets->spreadsheets->batchUpdate($sheetId, $batchUpdateRequest);

This can (and should) be scaled/scoped/refactored based on individual needs. It's fine for 1-3 datasets in a straight script, but if this were part of a larger function/system you would want to chunk this out accordingly and reuse code.

IMPORTANT NOTE: Take care in deciding whether or not to omit the index property when creating the sheets. If the 0 index is not included in the batch request, the default "Sheet1" will remain in the parent sheet and will be the first visible/in order.

With the above, I now have a parent "sheet" with the appropriate name/title/etc., but that sheet now has two named tabs based on the values submitted in the batch request.

Great, but how do I work with the multiple sheets?

Glad you asked. As far as pushing data into each of the tabs, it's super simple. When we created the sheets, we gave them each a (hopefully) unique title which can be used as the value of $sheetNameAndRange as the parent identifier.

In my own case, I have a foreach which processes the $sheets->spreadsheets_values->update request for all of the files submitted, and which ultimately does this as the data is loaded from each file:

$sheetNameAndRange = basename($localFile, '.csv');

Since the value of $sheetNameAndRange during each iteration in the foreach will match an existing sheet in the parent sheet, each of the appropriate datasets will find its proper home.

I'm sure there are other ways to accomplish this (with the sheet's ID for example); given my usually discrete input files the name pattern matching has totally solved the problem.

What's been particularly awesome about the multiple sheets in a parent sheet is that I'm finally able to significantly simplify several existing processes and ease the technical burden on the end users consuming said data. Taking the time to flesh out these additional details has paid off greatly!