Introdução

É muito comum o cenário onde precisamos consumir alguma fonte de dados em formato CSV ou planilha do excel. Como alternativa, é possível consumir uma planilha armazenada no Google Drive; isso eliminará um fluxo de importação/exportação da planilha e facilitará o trabalho colaborativo, uma vez que todos podem editar a mesma planilha. Vamos desenvolver a aplicação teste utilizando a versão 5.7 do Laravel, que foi lançada no mesmo dia em que escrevi esse artigo. Para obter uma instalação fresh siga as instruções da documentação.

API e permissões

O primeiro passo é criar um projeto dentro do Google Cloud Platform. Para isso, clique no botão “Selecione um projeto” ao lado do título da página, como mostrado na figura abaixo:

Como eu já tenho projetos criados, ele me abre um modal contendo todos os projetos e a opção desejada de “Novo Projeto” no canto superior direito. Pode ser que apareça algo ligeiramente diferente para usuários que ainda não têm projetos criados – procure uma opção similar.

Selecione um nome para o seu projeto.

Nesse momento o seu projeto já deve estar criado. Selecione o projeto recém criado e prossiga em “ativar apis e serviços”.

Você deve ativar a API do “Google Sheets” pesquisando por “Google Sheets” e entrando na opção correta. Após isso aperte o botão “Ativar”.

Agora, com o projeto criado e as APIs ativadas devemos criar as devidas credenciais para que nossa aplicação consiga se autenticar e consumir as APIs. Para isso, vá em “Credenciais” > “Criar credenciais” > “Chave da conta de serviço“.

Como você ainda não tem uma conta de serviço para esse projeto, você deve selecionar “Nova conta de serviço” no primeiro campo. Escolha um nome que faça sentido e escolha o papel de “Editor“, dentro de “Projeto” e selecione também o tipo de chave json.

Agora toda a parte de configuração das APIs está feita. Para finalizar precisamos criar uma planilha no drive e dar permissão para a conta de serviço que criamos colaborar nessa planilha. Para fazer isso podemos abrir o arquivo json baixado e procurar o “client_email“. Para esse e-mail que daremos a permissão de editar a planilha.

Vamos preencher a planilha com alguns itens:

Aplicação

Com a aplicação instalada utilizaremos o pacote asimlqt/php-google-spreadsheet-client para consumir a planilha e o pacote google/apiclient para fazer a autenticação. Adicione as dependências ao seu composer.json e depois rode o comando “composer update”.

{
    "require": {
        "asimlqt/php-google-spreadsheet-client": "3.0.*",
        "google/apiclient": "^2.2"
    }
}

Vamos dividir a aplicação em duas partes; um código que retorna o access token e uma classe que consome a planilha de fato. O código do access token deverá ser executado e a classe “injetada” de acordo com a necessidade. Para que isso seja feito com facilidade, vamos utilizar o service container do Laravel.

Código do access token

Vamos adicionar o código abaixo no metodo register() do AppServiceProvider:

$this->app->singleton('access.token', function () {
    return cache()->remember('spreadsheet', 60 * 24, function () {
        putenv('GOOGLE_APPLICATION_CREDENTIALS=' . storage_path('app/credentials.json'));
        $client = new \Google_Client;
        $client->useApplicationDefaultCredentials();

        $client->setApplicationName('App Test');
        $client->setScopes([
            'https://spreadsheets.google.com/feeds'
        ]);

        if ($client->isAccessTokenExpired()) {
            $client->refreshTokenWithAssertion();
        }

        return $client->fetchAccessTokenWithAssertion()['access_token'];
    });
});

Com o código acima, sempre que precisarmos de um access token, para qualquer tipo de serviço do Google, chamaremos o metodo resolve() passando a string “access.token” como parâmetro – isso deve retornar o access token.

Note que na primeira linha existe um cache de 24 horas (60 * 24 minutos); essa é uma boa ideia porque evita que a aplicação faça uma requisição externa sempre que o access token for requisitado por um serviço.

No código do access token é possível notar a seguinte linha:

putenv('GOOGLE_APPLICATION_CREDENTIALS=' . storage_path('app/credentials.json'));

Utilizar a função putenv() tem um efeito similar a adicionar o path das credenciais no arquivo .env, lembrando que para essa linha funcionar, você deve mover o arquivo .json do Google para dentro da pasta storage/app com o nome credentials.json.

É importante separar o código mostrado do código que consome a planila. Se decidirmos fazer uma classe que cria documentos no Drive do Google, podemos reutilizar o código para essa classe também. A única coisa que deveríamos fazer, é habilitar a API do Drive e adicionar o scope adequado no método setScopes(). Ficaria dessa forma:

$client->setScopes([
    'https://www.googleapis.com/auth/drive',
    'https://spreadsheets.google.com/feeds'
]);

O código que retorna o access token está pronto, agora já deve ser possível obter um resultado similar a esse pelo tinker.

Executei a função resolve duas vezes seguidas; repare que da primeira vez o retorno demorou alguns segundos, e da segunda vez foi instantâneo, essa é a economia de tempo que o cache nos trás.

Classe que consome a planilha

Vamos concentrar a lógica de consumo da planilha dentro de uma classe chamada SheetService que tem o objetivo de resumir a complexidade da utilização do pacote asimlqt/php-google-spreadsheet-client deixando a interface mais simples e implementando cache para evitar consumo de um webservice externo desnecessariamente.

<?php

namespace App\Services;

use Google\Spreadsheet\Worksheet;
use Google\Spreadsheet\SpreadsheetService;
use Google\Spreadsheet\ServiceRequestFactory;
use Google\Spreadsheet\DefaultServiceRequest;
use Illuminate\Support\Collection;

class SheetService
{
    protected $collection;
    protected $spreadsheetService;

    public function __construct(SpreadsheetService $spreadsheetService)
    {
        $this->spreadsheetService = $spreadsheetService;
        $this->setAccessToken();
        $this->setCachedCollection();
    }

    protected function setAccessToken()
    {
        $accessToken = resolve('access.token');
        ServiceRequestFactory::setInstance(
            new DefaultServiceRequest($accessToken)
        );
    }

    protected function setCachedCollection()
    {
        $this->collection
            = cache()->remember('spreadsheet-collection', 60 * 24, function () {
                return $this->combineHeaders();
            });
    }

    protected function combineHeaders()
    {
        $cellFeed = $this->getFromApi();
        $headers = array_shift($cellFeed);
        return collect(array_map(function ($cell) use ($headers) {
            return array_combine($headers, $cell);
        }, $cellFeed));
    }

    protected function getFromApi()
    {
        return $this->spreadsheetService->getSpreadsheetFeed()
            ->getByTitle('app-teste')
            ->getWorksheetFeed()
            ->getByTitle('Página1')
            ->getCellFeed()
            ->toArray();
    }

    public function get()
    {
        return $this->collection;
    }

    public function find($id)
    {
        return $this->get()
            ->where('Id', $id)
            ->first();
    }
}

Em resumo, o construtor da classe acima inicializa a classe setando uma instância de \Google\Spreadsheet\DefaultServiceRequest com o access token retornado, além de cachear uma coleção construída a partir do retorno da API:

O método getFromApi faz a consulta da planilha de fato e retorna um array. Nele, precisamos especificar o título e a aba da planilha através dos métodos getByTitle().

O método combineHeaders constrói uma instância de \Illuminate\Support\Collection contendo o cabeçalho da planilha como índices dos elementos.

A propriedade collection sempre vai conter a instância de \Illuminate\Support\Collection retornada do cache, isso é importante porque torna o código rápido e permite a manipulação da coleção utilizando uma interface muito simples e semelhante à do Eloquent.

Todos os métodos públicos da Classe são construídos sobre a propriedade $collection garantindo que o cache sempre será utilizado.

Agora a classe já está pronta. É possível obter resultados iguais a esses chamando os métodos get e find pelo tinker:

O método find recebe o id da pessoa específica e retorna apenas o registro dela?

Aplicabilidade

Vamos construir uma simples API sobre essa classe que criamos. Primeiro vamos criar as seguintes rotas:

Route::get('/pessoas', 'AppController@index');
Route::get('/pessoas/{id}', 'AppController@show');

Com o comando php artisan make:controller AppController podemos gerar uma controller e depois implementar os métodos index e show da seguinte forma:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Services\SheetService;

class AppController extends Controller
{
    public function index(SheetService $sheetService)
    {
        return $sheetService->get();
    }

    public function show($id, SheetService $sheetService)
    {
        return $sheetService->find($id);
    }
}

A partir de agora temos uma simples API funcionando. Apenas para enfatizar o consumo da planilha, se entrarmos no endpoint /pessoas, devemos ver algo similar ao seguinte:

Desvantagens

Caso a sua planilha comece a conter milhares de linhas pode se tornar inviável trafegar isso pela rede enquanto o usuário espera, nesse caso um banco de dados seria mais adequado. Outra situação que pode ser desajeitada é a necessidade de normalizar dados, uma vez que a aplicação não controla o que entra na planilha, essa normalização deverá ser implementada na planilha, fugindo completamente do versionamento e tornando o processo propício a erros.

Conclusão

Essa abordagem contempla a manipulação de planilhas simples e facilita a implementação do sistema evitando a necessidade de um banco de dados, bem como um fluxo de importação/exportação para alimentar esse banco.