Skip to main content

Site links

  • Agency for web development
  • Content Management with Primer
  • Open Source Leadership with Drupal
Language
DeutschEnglish

Secondary navigation

  • Contact
Agentur für Webentwicklung

Hauptnavigation

  • References
  • Services
  • Blog
  • About us

Site links

  • Agency for web development
  • Content Management with Primer
  • Open Source Leadership with Drupal
close

Breadcrumb

  1. Home
  2. Blog
  3. Techblog Archive

Use API access to Google Spreadsheets to automate processes

16. August 2013
Sascha Grossenbacher Porträt
Sascha Grossenbacher

Use case

You have a long list in a Google spreadsheet that needs to be processed and the results written back to the spreadsheet. This article describes how the Spreadsheets API can be used to automate that process. Our spreadsheets contains two relevant columns, Data and Result. We're going to fill the result column based on the data.

How it works

The example code below uses the list-based API to retrieve a set of rows, do something with them and then write them back. That API requires that your spreadsheet has a header column as columns are identified using their header name.

We rely on the Zend_GData component, which is no longer actively maintained and might no longer work in the future. For the time being, it still works great.

The script requires three arguments, the user name, password, and the spreadsheet key. To avoid having to load too much into memory or reaching other limits, we search for 10 rows that don't have a result, process them, write the results back and repeat that until we can't find any remaining rows that don't have results.

Note that the list-based API operates on whole rows. Making manual changes on the same row while the script is processing it will result in a HTTP 409 error, so be careful with manual edits in the spreadsheet while the script runs.

The code

setSpreadsheetKey($key); // Optionally specify a worksheet id, otherwise it uses the default. // $query->setWorksheetId($this->getWorksheetId()); // Search for rows that have no result and limit to 10 results. $query->setSpreadsheetQuery('result=""'); $query->setMaxResults(10); $feed = $client->getListFeed($query); if ($feed->count()) { // If we have results, do something with them. $data = $entry->getCustomByName('data')->getText(); print "Processing " . $data . ".... "; $result = $data * 2; // Update the row and save. $entry->getCustomByName('result')->setText($result); $entry->save(); print "OK\n"; } else { print "Finished\n"; } } while ($feed->count()); ?>

About MD Systems

MD Systems, headquartered in Zurich, is a unique team of international open source initiative leaders for the Drupal content management system.

With our experts for software architecture and design and our industry solutions, you digitize your organization successfully and efficiently.

MD Systems GmbH

Hermetschloostrasse 77, CH-8048 Zürich

Schweiz

+41 44 500 45 95

[email protected]

Fußzeile

  • Contact
  • Impressum
  • Data protection
To top

© Copyright 2017 - 2018 MD Systems GmbH. Alle Rechte vorbehalten. Erstellt mit PRIMER - powered by Drupal.