Perl + Google

[k-maub]

Hello all,

What I'm really keen on doing (despite the excellent addition of data logging capability) is to read off the data from IOBridge with Perl, and add that data (maybe after some manipulation) to a Google Spreadsheet.  There are some CPAN modules that claim to have the ability to do this, but I cannot even login to google with them (Net::Google::Spreadsheets or Net::Google::AuthSub).

Has anyone had any experience with these?  I have not had any luck finding help on the rest of the web, so I'm hoping someone here might be able to offer a little guidance.

Thanks!
ryan

[k-maub]

Ok, after many trial and tribulations, I've debugged all the issues I've been having with the different CPAN modules I needed to do this.  I set up my own data logging Perl code that runs under Cygwin on my Windows machine.  It time-averages and converts raw data, and then records and graphs the data online on Google Spreadsheets:

Code: [Select]

#!/usr/bin/perl

use strict;
use LWP::Simple;
use JSON -support_by_pp;
use Net::Google::Spreadsheets;

my $GoogleID = "YOUR GOOGLE USERNAME";
my $GooglePW = "YOUR GOOGLE PASSWORD";
my $GoogleKey = "YOUR GOOGLE CHART KEY";
my $Worksheet_Title = 'YOUR WORKSHEET NAME';
my $WaitTime = 5;
my $TimePeriod = 60*10;  # Number of seconds between data logging
my $MeasureFreq = 10;   # Number of data points from ioBridge to average
my $SleepTime = $TimePeriod/$MeasureFreq;

my $jsonurl = 'YOUR MODULES URL';

sub Convert_Raw_Value {

  # Do whatever conversion you need for your application

  my $RawValue = @_[0];
  my $Ohms = 10.0e3 * $RawValue / (1023.-$RawValue);
  my $microSiemens = 1/$Ohms * 1e6;
  $microSiemens / 56*257;
 
}

my $service = Net::Google::Spreadsheets->new(
  username => $GoogleID."\@gmail.com",
  password => $GooglePW,
);

my $spreadsheet = $service->spreadsheet(
  {
    key => $GoogleKey,
  }
);

my $worksheet=$spreadsheet->worksheet(
  {
    title => $Worksheet_Title,
  }
);

  # update cell by batch request
  # define the headers at the top of the worksheet
  $worksheet->batchupdate_cell(
    {row => 1, col => 1, input_value => 'DateTime'},
    {row => 1, col => 2, input_value => 'TDS'},
    {row => 1, col => 3, input_value => 'Comment'},
    {row => 1, col => 4, input_value => 'Raw Values'},
  );


my $content;
my $AI1_avg;
my $Converted_Value;

while (1) {

  $AI1_avg = 0;

 for (1..$MeasureFreq) {

 while (1) {

   $content = get $jsonurl;
   if (! defined $content) {
     print "Didn't work... waiting $WaitTime seconds and trying again\n";
     sleep $WaitTime;
   } else {
     last;
   }
 
 }

  my $json = new JSON;

  my $json_text =

$json->allow_nonref->utf8->relaxed->escape_slash->loose->allow_singlequote->allow_barekey->decode($content);

  my $AI1 = $json_text->{module}->{channels}[0]->{AnalogInput};
  $AI1_avg = $AI1_avg + $AI1;
 
  sleep $SleepTime;
 
}

  $AI1_avg = $AI1_avg / $MeasureFreq;

  my($SecondTime, $MinuteTime, $HourTime, $DayDate, $MonthDate, $YearDate, $wday, $yday, $isdst) = localtime;
  $YearDate+=1900;   # Needed correction from "localtime"
  $MonthDate++;      # Needed correction from "localtime"

  my $DateTime = sprintf "%02d/%02d/%4d %02d:%02d:%02d", $MonthDate, $DayDate, $YearDate, $HourTime, $MinuteTime,

$SecondTime;

  $Converted_Value = &Convert_Raw_Value($AI1_avg);
  print "$DateTime  $Converted_Value  $AI1_avg\n";
 
  # add a row
  my $new_row = $worksheet->add_row(
    {
        datetime => "$DateTime",
        tds  => "$Converted_Value",
        comment => '',
        rawvalues => "$AI1_avg",
    }
  );

}

This offers several advantages over the native data-logging, including being able to manipulate your raw values as needed, annotating the graphs, and getting to explore and probe your data on a kicking time series chart.  However, because it uses Flash, the ioBridge page is still the way to go from the iPhone.

I'm currently doing my best to measure total dissolved solids in my aquarium.  This is what the final product looks like (updates every five minutes):
http://spreadsheets.google.com/pub?key=tKdPMK-KjqI4jwDhet8vc4Q&single=true&gid=2&output=html
I don't really use iGoogle, but it looks like you can easily put charts that are important to you on your home page.