Integração de dados do Google Spreadsheets com PHP, Parte 2

Use PHP para processar e integrar alimentação de dados do Google Spreadsheets com um aplicativo da Web

O Google Spreadsheets é um aplicativo de planilha baseado em nuvem muito conhecido. Os desenvolvedores de aplicativos podem acessar e procurar dados de planilhas on-line através do Google Spreadsheets Data API. Este artigo apresenta e demonstra o Google Spreadsheets Data API em um contexto de aplicativo PHP, explicando como incluir, modificar e excluir diferentes elementos do conteúdo da planilha.

Introdução

Acrônimos usados frequentemente

  • API: Application program interface
  • DOM: Document Object Model
  • GUI: Graphical User Interface
  • PDO: PHP Data Objects
  • REST: Representational State Transfer
  • RSS: Really Simple Syndication
  • SQL:Structured Query Language
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

Na primeira parte deste artigo, apresentei o Google Spreadsheets Data API, uma API REST que permite que desenvolvedores construam rapidamente novos aplicativos em torno dos dados da planilha do espaço do usuário, hospedados na nuvem do Google Spreadsheets. Expliquei as noções básicas de planilha, lista e alimentação de célula, ilustrando como é possível usar essas alimentações com o Zend Framework para obter de modo rápido e eficiente o conteúdo da planilha para o aplicativo PHP.

O Google Spreadsheets Data API, no entanto, não suporta apenas recuperação e procura de planilhas. Nesta parte final, examino como é possível usar o Data API para manipular remotamente o conteúdo, incluindo, atualizando e excluindo linhas, células e planilhas de um aplicativo PHP remoto. As técnicas descritas neste artigo são complementadas por dois aplicativos de exemplo que, respectivamente, leem e importam alimentações RSS e conjuntos de resultados de SQL no Google Spreadsheet, usando o Data API.


Adição de planilhas

Cada planilha contém uma ou mais planilhas, e o Google Spreadsheets Data API permite acessar e manipular essas planilhas programaticamente. Para ver como isso funciona, vá até o serviço Google Spreadsheets, faça o login e crie uma planilha vazia. Observe a chave da planilha, que deve ser visível na URL. Sua planilha vazia é semelhante a Figura 1.

Figura 1. Uma planilha nova e vazia
Screen capture of a new, empty Google spreadsheet

Para incluir uma nova planilha nessa planilha, é preciso gerar uma nova entrada de planilha no formato Atom e depois usar o POST nessa entrada para postar na URL de planilha da planilha especificada. Considere Lista 1, que ilustra o processo.

Lista 1. Inclusão de uma nova planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Creating worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheet feed for this spreadsheet
      $wsFeed = $service->getWorksheetFeed($ssEntry);

      // create new entry
      $doc  = new DOMDocument();
      $doc->formatOutput = true;
      $entry = $doc->createElement('atom:entry');
      $entry->setAttributeNS('http://www.w3.org/2000/xmlns/' ,
        'xmlns:atom', 'http://www.w3.org/2005/Atom');
      $entry->setAttributeNS('http://www.w3.org/2000/xmlns/' ,
        'xmlns:gs', 'http://schemas.google.com/spreadsheets/2006');
      $doc->appendChild($entry);

      // add title, row and column counts
      $title = $doc->createElement('atom:title', 'Jan 2011');
      $entry->appendChild($title);
      $rows = $doc->createElement('gs:rowCount', '10');
      $entry->appendChild($rows);
      $cols = $doc->createElement('gs:colCount', '10');
      $entry->appendChild($cols);

      // insert entry
      $entryResult = $service->insertEntry($doc->saveXML(), 
        $wsFeed->getLink('self')->getHref());
      echo 'The ID of the new worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

É importante observar no início que o componente Zend_Gdata_Spreadsheets não fornece (ainda) métodos nativos para incluir ou atualizar planilhas. Mas ainda é possível fazer isso. Você deve criar manualmente o elemento da planilha XML e usar o método genérico insertEntry() para usar o POST para postar na URL correta.

Lista 1 primeiro obtém uma referência à entrada da planilha nomeada e depois obtém a alimentação da planilha dessa entrada. Em seguida, um objeto DOMDocument é criado para representar a entrada real, e os elementos são incluídos nele para especificar o título, número de linha e número de colunas da planilha. Finalmente, o método insertEntry() envia o fragmento de XML para a URL da alimentação da planilha, para que as mudanças sejam confirmadas na planilha.

Quando você executa esse script e inverte para a GUI do Google Spreadsheets, você vê uma nova planilha dentro da sua planilha vazia, como em Figura 2.

Figura 2. A planilha com uma nova planilha incluída
Screen capture of the spreadsheet with a new worksheet labeled 'Jan 2011'

Outra abordagem dessa tarefa é criar uma instância do objeto Zend_Gdata_Spreadsheets_WorksheetEntry, configurar suas propriedades e depois postar esse objeto de entrada na URL de alimentação da planilha. O resultado final é o mesmo, mas seguir essa abordagem é mais simples, já que o Zend_Gdata_Spreadsheets toma conta dos detalhes de gerar o XML de entrada da planilha. Considere Lista 2, que demonstra essa abordagem alternativa para produzir uma saída equivalente a Lista 1.

Lista 2. Inclusão de uma nova planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Creating worksheets</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheet feed for this spreadsheet
      $wsFeed = $service->getWorksheetFeed($ssEntry);

      // create new entry
      $wsEntry = new Zend_Gdata_Spreadsheets_WorksheetEntry();
      $title = new Zend_Gdata_App_Extension_Title('Jan 2011');
      $wsEntry->setTitle($title);
      $row = new Zend_Gdata_Spreadsheets_Extension_RowCount('10');
      $wsEntry->setRowCount($row);
      $col = new Zend_Gdata_Spreadsheets_Extension_ColCount('10');
      $wsEntry->setColumnCount($col);

      // insert entry
      $entryResult = $service->insertEntry($wsEntry, 
        $wsFeed->getLink('self')->getHref());
      echo 'The ID of the new worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

Lista 2 cria objetos Zend_Gdata_Spreadsheets_Extension para o título da planilha, contagem de linhas e contagem de colunas e depois anexa esses objetos ao objeto Zend_Gdata_Spreadsheets_WorksheetEntry. Essa abordagem é mais simples de entender e menos chata de implementar. O objeto final de entrada é então transmitido para a URL de alimentação da planilha usando o método insertEntry() , como descrito anteriormente.


Atualização e exclusão de planilhas

Além de incluir planilhas, o Google Spreadsheets Data API também suporta programaticamente a atualização e remoção de planilhas. Essas duas operações exigem que o usuário recupere a entrada da planilha a ser modificada ou excluída, faça as mudanças na entrada, como exigido, e depois a transmita de volta ao servidor para execução. A natureza da solicitação—PUT ou DELETE—determina se a entrada da planilha foi atualizada ou excluída.

Para ver como isso funcionada, considere Lista 3, que atualiza o título da planilha criada em Lista 1. Observe que para essa listagem e para as listagens subsequentes, é necessário possuir a chave da planilha, que pode ser obtida da alimentação da planilha ou do objeto de entrada retornado pelo método insertEntry() .

Lista 3. Atualização de uma planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    table, td {
      border: 1px solid black;
      vertical-align: top;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get worksheet entry
      $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $wsEntry = $service->getWorksheetEntry($query);
      $title = new Zend_Gdata_App_Extension_Title('Feb 2012');
      $wsEntry->setTitle($title);

      // update entry
      $entryResult = $service->updateEntry($wsEntry, 
        $wsEntry->getLink('edit')->getHref());
      echo 'The ID of the updated worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

Lista 3 usa o método getWorksheetEntry() visto em exemplos anteriores para recuperar uma entrada específica da planilha por meio de seu identificador exclusivo. O métodosetTitle() do objeto de entrada altera o título, e a entrada revisada é então transmitida de volta, usando o métodoupdateEnry() . Observe que a revisão deve ser enviada para a URL de edição especificada pelo objeto de entrada.

Seguindo a execução desse procedimento, será possível ver a planilha revisada na GUI do Google Spreadsheets em Figura 3.

Figura 3. A planilha com a nova planilha atualizada
Screen caputre of the spreadsheet with the name of the new worksheet updated to 'Feb 2012'

Para excluir uma planilha, basta chamar o método delete() da entrada correspondente da planilha. Lista 4 contém um exemplo de exclusão de planilha da Lista 3.

Lista 4. Exclusão de uma planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Deleting worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get worksheet entry
      $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $wsEntry = $service->getWorksheetEntry($query);

      // delete entry
      $service->delete($wsEntry->getLink('edit')->getHref());
      echo 'The worksheet entry has been deleted';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Como antes, esse script recupera a entrada da planilha e depois chama o método delete() para transmitir uma solicitação DELETE para a URL de edição da entrada. Essa solicitação é interpretada e executada pelos servidores da API do Google, resultando na remoção da planilha da planilha principal. Essa etapa é claramente visível em Figura 4, que mostra a planilha restaurada ao seu estado original.

Figura 4. A planilha depois da exclusão da nova planilha
Screen capture of the spreadsheet after deleting the new worksheet

Inclusão de linhas na planilha

O Google Spreadsheets Data API também torna possível incluir linhas em uma planilha, e, dessa vez, o componente Zend_Gdata_Spreadsheets possui um método nativo para executar essa operação. Para incluir uma linha, crie um novo objeto Zend_Gdata_Spreadsheets_ListEntry representando a linha, preencha-a com valores e depois use o POST nesse objeto para postar na URL de alimentação da lista. Lista 5 contém um exemplo.

Lista 5. Inclusão de linhas na planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Adding worksheet rows</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // create row content
      $row = array(
        "date" => "24-12-2010", 
        "task" => "Server reconfiguration", 
        "hours" => "3.5"
      );

      // insert new row
      $entryResult = $service->insertRow($row, $ssKey, $wsKey);
      echo 'The ID of the new row entry is: ' . $entryResult->id;
      
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Lista 5 configura a planilha de destino e depois cria uma array de dados da linha contendo mapeamentos de valor da coluna. Essa array é passada para o método insertRow() , que cria o objeto de entrada necessário e o posta na URL de alimentação. Figura 5 exibe o resultado na GUI do Google Spreadsheets. (Veja uma versão ampliada da figura 5.)

Figura 5. A planilha com novas linhas incluídas
Screen capture of the spreadsheet with a new column heading and a new data row added

Observe esses avisos importantes quando adicionar linhas programaticamente:

  • A planilha de destino já deve existir e já deve ter os campos da linha de cabeçalho definidos.
  • A array da linha passada para insertRow() deve incluir chaves correspondentes às colunas da planilha de destino; apenas fornecer valores não é suficiente e produz um erro de namespace.
  • Somente esses campos com linhas de cabeçalho com chaves correspondentes na planilha de destino são inseridos.
  • As chaves da array da linha não devem estar em letras maiúsculas, já que esse formato normalmente gera um erro do servidor e falha na inserção da linha.

Atualização e exclusão de linhas

Também é possível atualizar e excluir linhas usando o Data API. Para fazer isso, você deve recuperar a entrada a linha a ser modificada, fazer as alterações nela e depois transmiti-la de volta para a URL de alimentação da lista. Se a solicitação foi enviada como PUT ou DELETE é que decide se a linha será modificada ou excluída da planilha.

Lista 6 ilustra o processo de atualização de linhas em uma planilha.

Lista 6. Atualização de linhas da planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheet rows</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get rows matching query
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $query->setSpreadsheetQuery('salesunits > 25000')
      $listFeed = $service->getListFeed($query);

      // iterate over matching rows
      // increase sales units by 50%
      // write updated rows back to spreadsheet
      foreach ($listFeed as $listEntry) {
        $rowData = $listEntry->getCustom(); 
        $newRow = array();
        foreach($rowData as $field) {
          $newRow[$field->getColumnName()] = $field->getText();
          if ($field->getColumnName() == 'salesunits') {
            $newRow[$field->getColumnName()] = $field->getText()*1.5;
          }
        }
        $entryResult = $service->updateRow($listEntry, $newRow);
      }
      echo 'Rows successfully updated.';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Lista 6 cria uma consulta de lista que retorna todas as linhas da planilha com unidades de venda acima de um limite particular como uma alimentação de lista. Ela itera essa alimentação, processando uma linha por vez e ajustando o valor de certas células em cada linha. A linha atualizada é gravada de volta na planilha através do método updateRow() .

Figura 6 ilustra a planilha antes e depois da atualização. (Veja uma versão ampliada da figura 6.)

Figura 6. A planilha com algumas linhas atualizadas
Screen capture of the worksheet with some rows updated

Excluir linhas segue um processo semelhante, exceto que, em vez de chamar o método updateRow() do objeto de serviço, você chama o método delete() da entrada da linha. Lista 7 ilustra isso, excluindo a última linha da planilha especificada.

Lista 7. Exclusão de linhas da planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Deleting worksheet rows</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get list feed
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $listFeed = $service->getListFeed($query);  

      // get and delete last row
      $lastEntry = $listFeed->offsetGet($listFeed->count()-1);
      $lastEntry->delete(); 
      echo 'Row successfully deleted.';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Figura 7 ilustra a planilha antes e depois da atualização. (Veja uma versão ampliada da figura 7.)

Figura 7. A planilha com uma linha excluída
Screen caputure of the worksheet with a row deleted

Aplicativo de exemplo: conversor de RSS

Agora que você sabe como incluir e excluir linhas, vamos tentar construir um aplicativo simples que demonstre isso na prática. Você irá criar um conversor de RSS para planilha, que recupera todas as informações de notícias de uma alimentação RSS remota, extrai metadados das informações da alimentação e os coloca em linhas e colunas da planilha.

Antes de começar, volte à GUI do Google Spreadsheets e crie uma planilha vazia com uma linha de cabeçalho correspondente aos campos de uma típica alimentação RSS. Os resultados devem ser semelhantes a Figura 8.

Figura 8. Uma planilha vazia
Screen capture of an empty worksheet with column headings and no data

A alimentação de exemplo neste caso é a alimentação de notícias da BBC do Reino Unido, que contém breves descrições das informações das notícias mais importantes no Reino Unido. Com o componente Zend_Feed do Zend Framework, é bastante fácil analisar a alimentação das notícias, extrair informações dela e depois gravá-la no Google Spreadsheet usando o Data API. Lista 8 contém o código.

Lista 8. Importação de linhas de planilha de uma alimentação RSS
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Importing worksheet rows from an RSS feed</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
    Zend_Loader::loadClass('Zend_Feed_Rss');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // consume RSS feed
      // insert each channel item as a spreadsheet row
      // if large feed, increase PHP script execution time
      $channel = new Zend_Feed_Rss('http://feeds.bbci.co.uk/news/uk/rss.xml');
      foreach ($channel as $item) {
          $row = array();
          $row['title'] = $item->title();
          $row['description'] = $item->description();
          $row['link'] = $item->link();
          $row['date'] = $item->pubDate();
          $entryResult = $service->insertRow($row, $ssKey, $wsKey);
      }    

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Analisar alimentações RSS é muito fácil com o componente Zend_Feed: basta passar a URL de alimentação RSS para o construtor do objeto, e o construtor recupera a alimentação, analisa-a e a converte em uma série de objetos que podem ser iterados. Lista 8 faz exatamente isso, iterando os itens na alimentação e criando uma array para cada um. Essa array é inserida na planilha como linha, usando o método insertRow() . O processo continua até que todos os itens na alimentação RSS sejam processados.

Figura 9 mostra como o resultado se parece.

Figura 9. Uma planilha com linhas de uma alimentação RSS
Screen capture of a worksheet with data rows populated from an RSS feed

Observe que o Data API atualmente não permite inserções em lote de dados de linha. Se a ordem das linhas não for importante, no entanto, é possível enviar solicitações em paralelo, para agilizar o processamento.


Modificação de células da planilha

Assim como é possível modificar linhas da planilha, também é possível modificar células da planilha. Como você já deve ter adivinhado, para realizar essa modificação é preciso enviar uma entrada revisada para a alimentação da célula da planilha, contendo detalhes da célula a ser atualizada.

O método updateCell() do objeto de serviço atualiza as células. Esse método aceita cinco argumentos: o índice da linha, o índice da coluna, o valor da célula, a chave da planilha e a chave da planilha principal. Os índices de linha e coluna começam em 1. Lista 9 ilustra como isso funciona.

Lista 9. Atualização de células da planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheet cells</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // update cell at row 6, column 5
      $entry = $service->updateCell('6', '5', 'Hello, world', $ssKey, $wsKey);
      echo 'Updated cell ' . $entry->getTitle()->getText() . '
'; // clear cell at row 1, column 1 $entry = $service->updateCell('1', '1', '', $ssKey, $wsKey); echo 'Cleared cell ' . $entry->getTitle()->getText(); } catch (Exception $e) { die('ERROR: ' . $e->getMessage()); } ?> </body> <html>

A chamada para updateCell() retorna um objeto de entrada, que pode ser analisado em busca de informações específicas. Em particular, observe que o título do objeto de entrada da célula retorna as coordenadas alfanuméricas da célula que foi atualizada.

Figura 10 ilustra a saída do script.

Figura 10. O resultado da atualização da célula
Screen capure of the result of updating a cell indicates: Updated cell E6, Cleared cell A1

Figura 11 mostra o resultado na GUI do Google Spreadsheets.

Figura 11. Uma planilha com células atualizadas
Screen capure of a worksheet with updated cells (E6 and A1)

Outra abordagem seria usar uma consulta na célula para retornar uma célula específica e depois atualizar a célula usando o métodosetInputValue() . Observe que também é possível inserir fórmulas em vez de valores estáticos. Lista 10 demonstra isso.

Lista 10. Atualização de células da planilha
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheet cells</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get cell feed
      // restrict feed to single cell E4
      $query = new Zend_Gdata_Spreadsheets_CellQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $query->setMinRow(4);      
      $query->setMaxRow(4);      
      $query->setMinCol(5);      
      $query->setMaxCol(5);      
      $cellFeed = $service->getCellFeed($query);  

      // get cell from query
      $cellEntry = $cellFeed->offsetGet(0); 

      // update cell value and save back to spreadsheet
      $cellEntry->getCell()->setInputValue('=B2');
      $service->updateEntry($cellEntry, $cellEntry->getLink('edit')->getHref());
      echo 'Updated cell ' . $cellEntry->getTitle()->getText();      
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Neste caso, o valor da célula é definido como uma fórmula que faz referência à outra célula e a entrada revisada é gravada de volta usando o método updateEntry() do objeto de serviço.


Aplicativo de exemplo: importador de banco de dados

Com todas essas informações em mãos, veja um outro exemplo: a importação de dados da planilha de um banco de dados MySQL. Leve em conta que você possui a estrutura do banco de dados em Figura 12. (Veja uma versão somente texto da figura 12.)

Figura 12. Um banco de dados de exemplo
Screen capture of an example database

É bastante fácil executar uma consulta SQL e depois criar células da planilha a partir de cada campo do conjunto de resultados da consulta. Considere Lista 11, que contém um exemplo em operação.

Lista 11. Importação de células da planilha de um banco de dados
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Importing worksheet cells from a database</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    // create PDO connection
    $dbh = new PDO('mysql:host=localhost;dbname=library', 'user', 'pass');
    $sql = "SELECT title, author FROM library";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get results from database
      // insert header row
      $rowCount = 1;
      $result = $dbh->query($sql, PDO::FETCH_ASSOC);
      for ($x=0; $x<$result->columnCount(); $x++) {
        $col = $result->getColumnMeta($x);
          $service->updateCell($rowCount, ($x+1), $col['name'], $ssKey, $wsKey);
      }
      $rowCount++;
      // insert each field of each row as a spreadsheet cell
      // if large result set, increase PHP script execution time
      foreach($result as $row) {
        $colCount=1;
        foreach ($row as $k=>$v) {        
          $service->updateCell($rowCount, $colCount, $v, $ssKey, $wsKey);
          $colCount++;
        }
        $rowCount++;
      }

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

Lista 11 inclui as classes necessárias e configura as credenciais do Data API. Ela cria uma conexão PDO com o banco de dados MySQL e formula uma consulta que retorna uma lista de autores e títulos. Ela também se conecta ao Spreadsheets Data API e cria uma conexão autenticada.

Em seguida, o script usa o método PDO getColumnMeta() para retornar os nomes das colunas do conjunto de resultados e grava os nomes das colunas na planilha como linha de cabeçalho, usando o método updateCell() . Ele começa, então, a iterar através do conjunto de resultados, gravando cada campo na planilha como uma célula separada e pulando para a próxima linha quando concluída a ação. Dessa maneira, o conteúdo de todo o conjunto de resultados é transferido para a planilha.

Figura 13 demonstra o resultado na GUI do Google Spreadsheets.

Figura 13. Uma planilha do Google gerada a partir de um conjunto de resultados do banco de dados
Screen capture of a Google spreadsheet generated from a database result set in Figure 12

Conclusão

O Google Spreadsheets Data API torna possível modificar dados na nuvem do Google Spreadsheets a partir de um aplicativo PHP. Tarefas comuns, como incluir, modificar e excluir planilhas, linhas e células, são suportadas e podem ser realizada usando o Zend_Gdata. Este artigo forneceu exemplos de todas essas tarefas, junto com dois exemplos de aplicativos: importadores de planilha para alimentações RSS e resultados da consulta do banco de dados.

Como ilustram esses exemplos, o Google Spreadsheets Data API é uma ferramenta eficiente e flexível para desenvolvedores que procuram construir novos aplicativos criativos em torno de dados de planilha na nuvem. O Data API está atualmente em desenvolvimento, então espere recursos adicionais mais interessantes para o futuro.

Recursos

Aprender

Obter produtos e tecnologias

Discutir

Comentários

developerWorks: Conecte-se

Los campos obligatorios están marcados con un asterisco (*).


Precisa de um ID IBM?
Esqueceu seu ID IBM?


Esqueceu sua senha?
Alterar sua senha

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


A primeira vez que você entrar no developerWorks, um perfil é criado para você. Informações no seu perfil (seu nome, país / região, e nome da empresa) é apresentado ao público e vai acompanhar qualquer conteúdo que você postar, a menos que você opte por esconder o nome da empresa. Você pode atualizar sua conta IBM a qualquer momento.

Todas as informações enviadas são seguras.

Elija su nombre para mostrar



Ao se conectar ao developerWorks pela primeira vez, é criado um perfil para você e é necessário selecionar um nome de exibição. O nome de exibição acompanhará o conteúdo que você postar no developerWorks.

Escolha um nome de exibição de 3 - 31 caracteres. Seu nome de exibição deve ser exclusivo na comunidade do developerWorks e não deve ser o seu endereço de email por motivo de privacidade.

Los campos obligatorios están marcados con un asterisco (*).

(Escolha um nome de exibição de 3 - 31 caracteres.)

Ao clicar em Enviar, você concorda com os termos e condições do developerWorks.

 


Todas as informações enviadas são seguras.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=80
Zone=Software livre
ArticleID=630801
ArticleTitle=Integração de dados do Google Spreadsheets com PHP, Parte 2
publish-date=12142010