How to Write to a Google Spreadsheet with a Perl Script

Google Docs is one of many cloud computing document-sharing services and provides an alternative to MS Office applications such as MS Excel. This post will show how to write data to Google Docs Spreadsheet and read data back from the same spreadsheet using perl programming language.

Google Sheets is an online spreadsheet application that lets you create and format spreadsheets and simultaneously work with other people. Here are the links for more information:
Overview of Google Sheets
Create and save a spreadsheet

First you need to create spreadsheet where the script will put some data. You need Google account for this. To create a new spreadsheet, go to your Google Drive , click the red ‘Create’ button, and select ‘Spreadsheet’ from the drop-down menu. As soon as you name the spreadsheet or start typing, Google Sheets will automatically save your work every few seconds.

At the top of the spreadsheet, you’ll see text that indicates when your spreadsheet was last saved. Now when you have spreadsheet you can call it in perl script by its name and write data to it. Perl module Net::Google::Spreadsheets provides all needed functions to do this. Below is the basic script that is opening spreadsheet, iterate through several rows and columns, multiply row index by column index and put it to the cell. Then it iterates again and read the values and print them to screen. Obviously you need to put real account information, spreadsheet and sheet names into this script to run it.

use Net::Google::Spreadsheets;
my $service = Net::Google::Spreadsheets->new( username => ‘xxxxxxx’, password => ‘xxxxxxx’ );
my @spreadsheets = $service->spreadsheets();
my $spreadsheet = $service->spreadsheet( { title => ‘spreadsheet_name’ } );
my $worksheet1 = $spreadsheet->worksheet( { title => ‘Sheet1’ } );

# get a cell
my $cell = $worksheet1->cell({col => 1, row => 5});
# update input value of a cell
$cell->input_value(‘new value’);
# print the value of cell
my $cell = $worksheet1->cell({col => 1, row => 5});
print “\n”;
print $cell->input_value;
print “\n”;
for($i=1; $i<4; $i++) { for($j=1; $j<4; $j++) { $cell = $worksheet1->cell({col => $i, row => $j}); $cell->input_value($i*$j);
} }
for($i=1; $i<4; $i++) { for($j=1; $j<4; $j++) { $cell = $worksheet1->cell({col => $i, row => $j});
print $cell->input_value;
print ” “;
}
print “\n”; }

To test that it really put values to spreadsheet you can access your spreadsheet at any time by opening your spreadsheet at Google Drive after you run perl script. So now we can create different programs that export or import data to spreadsheet.

References:

4 Free Alternatives To Microsoft Excel by Richard Wilson on May 8, 2013



1 thought on “How to Write to a Google Spreadsheet with a Perl Script

  1. Are you able to use Net::Google::Spreadsheets? When I call it I get an error “Any::Moose is deprecated. Please use Moo instead at /usr/local/share/perl5/Net/Google/Spreadsheets.pm line 3.”

Leave a Comment