Programmatically Push Data to Google Sheets

I've written in the past about the ability to ship files to Google Drive via its REST API and PHP, which is a super-cool process in and of itself. In the last few months, I've been moving more and more of our internal data shipping processes to Google Shared Drives for ease of end user access. As folks started working remotely, moving data to Google has solved several support burdens regarding access and such.

Now, it's important to note that as of the time of this writing, Google is in the process of deprecating the supportsTeamDrives arguments (they will no longer be required to be declared to work with Shared Drives), so it is possible those bits may need to be removed.

The Basic Steps

I've got a GitHub repo that was spun up a couple years back which will effectively 'demo' several components of using the API in PHP to do things, including the stuff in this process. Feel free to use it as a starting point for your own project(s).

The simple angle of pushing some data directly into Google Sheets boils down to:

• Create the client with proper scopes;
• Identify your drive/folder IDs, desired file name, etc.;
• Create the application/vnd.google-apps.spreadsheet object;
• Read in data (our source arrives as flat CSV files); and
• Update the sheet appropriately.

For the purposes of this post, I'm skipping to the third "step" and beyond since the first two steps are pretty well covered in the previous post about programmatically using Google Drive. The only additional bit of information I'd add is that you will need to add another scope to the service:

$client->addScope(Google_Service_Sheets::SPREADSHEETS);
$sheets = new Google_Service_Sheets($client);

Creating the Sheet

Once you've got the client and scope set, you'll use a bit of source like this to create the destination sheet:

// Creating a Team (Shared) Drive file...
$fileMetadata = new Google_Service_Drive_DriveFile(array(
      'name' => $teamDriveFileName,
      'mimeType' => 'application/vnd.google-apps.spreadsheet',
      'teamDriveId' => $teamDriveId,
      'parents' => array($teamDriveFolderId)
));
$newTeamDriveFile = $service->files->create($fileMetadata, array('fields' => 'id',
                  'supportsTeamDrives' => true));
$newTeamDriveFileId = $newTeamDriveFile->id;

Reading and "Uploading" Data

This example reads data from TestData.csv and "updates" the recently-created sheet with "replacement" data:

// Populating '$teamDriveFileName' with test data...
$sheetNameAndRange = 'Sheet1';
$sheetDocument = $sheets->spreadsheets->get($newTeamDriveFileId);
// Obtain a dataset from another document (a local CSV on the filesystem):
$localDataFile = __DIR__.'/TestData.csv';
$importCSV = array();
$handle = fopen($localDataFile, 'r');
while ($row = fgetcsv($handle, null, ',')) {
    $importCSV[] = $row;
}
$replacementData = new Google_Service_Sheets_ValueRange(['range' => $sheetNameAndRange,
                  'majorDimension' => 'ROWS','values' => $importCSV]);
$sheets->spreadsheets_values->update($newTeamDriveFileId,
                  $sheetNameAndRange,$replacementData,['valueInputOption' => 'USER_ENTERED']);

Voila! A File is Born!

If you were to browse to the identified location from your source, a freshly-created file should now be in place, with the test data!

That's the basics of getting some data into Google Sheets, but the next tech post in two weeks will have an additional bit of goodness regarding data in multiple sheets (or tabs) within the parent sheet! Stay tuned!