view release on metacpan or  search on metacpan

lib/App/DrivePlayer/SheetDB.pm  view on Meta::CPAN

# Private helpers
# ------------------------------------------------------------------

sub _sheets_api {
    my ($self) = @_;
    return Google::RestApi::SheetsApi4->new(api => $self->api);
}

sub _open {
    my ($self) = @_;
    die "No spreadsheet_id configured\n" unless $self->spreadsheet_id;

    # Use Drive API to verify the file exists and is not trashed before
    # opening via Sheets API (which happily operates on trashed files).
    my $drive = Google::RestApi::DriveApi3->new(api => $self->api);
    my $meta  = eval { $drive->file(id => $self->spreadsheet_id)->get(fields => 'id,trashed') };
    if ($@) {
        die "SHEET_NOT_FOUND: $@" if $@ =~ /404|not.?found/i;
        die $@;
    }
    die "SHEET_NOT_FOUND: spreadsheet has been trashed\n" if $meta->{trashed};

    return $self->_sheets_api->open_spreadsheet(id => $self->spreadsheet_id);
}

# Write a header row then all data rows to a named worksheet (full replace).
sub _write_worksheet {
    my ($self, $ss, $name, $properties, $rows) = @_;
    my $cols = $SHEET_PROPERTIES{$properties}{cols};

    my $n  = scalar @$rows;
    my $ws = $self->_ensure_worksheet($ss, $name, $n + 1);
    $ws->clear_values()->submit_requests();
    $ws->row(1, $cols);
    $ws->rows([2 .. $n + 1], $rows) if $n;
}

# Read a worksheet and return arrayref of hashrefs keyed by header row.
# Returns [] if the worksheet doesn't exist or is empty.
sub _read_worksheet {
    my ($self, $ss, $name) = @_;
    my $ws = eval { $ss->open_worksheet(name => $name) };
    if ($@) { $log->warn("Could not open worksheet '$name': $@") if $log; return [] }
    return [] unless $ws;

    $ws->enable_header_row();
    my $cols = $ws->tie_cols;
    tied(%$cols)->values();      # prefetch the columns.

    my @result;
    my $i = tied(%$cols)->iterator(from => 0);
    while (my $row = $i->iterate()) {
        tied(%$row)->values();
        last unless $row->{drive_id};
        push(@result, $row);
    }
    return \@result;
}

# Open a worksheet by name, creating it with enough rows if absent, or
# expand it if it already exists but the grid is too small for the data.
sub _ensure_worksheet {
    my ($self, $ss, $name, $needed_rows) = @_;
    # Default Google Sheets grid is 1000 rows; honour that for small data.
    $needed_rows = 1000 if !$needed_rows || $needed_rows < 1000;

    # Try to create; silently ignore the error if it already exists.
    # If the API call fails (e.g. sheet already exists), the failed addSheet
    # request is left in $ss's internal batch queue.  Clear it so that
    # subsequent submit_requests() calls don't re-send the stale request.
    eval { $ss->add_worksheet(
        name            => $name,
        grid_properties => { rows => $needed_rows },
    )->submit_requests() };
    delete $ss->{requests} if $@;

    my $ws = $ss->open_worksheet(name => $name);

    # Expand an existing sheet that may have been created with fewer rows.
    if ($needed_rows > 1000) {
        $ws->update_worksheet_properties(
            properties => { gridProperties => { rowCount => $needed_rows } },
            fields     => 'gridProperties.rowCount',
        )->submit_requests();
    }

    return $ws;
}

# Sanitise a folder name for use as a worksheet tab name.
# Google Sheets forbids [ ] * / \ ? : and limits names to 100 chars.
sub _ws_name {
    my ($name) = @_;
    $name =~ s{[\[\]*\/\\?:]}{}g;
    $name =~ s/^\s+|\s+$//g;
    $name = 'Folder' unless length $name;
    return substr($name, 0, 100);
}

1;

__END__

=head1 NAME

App::DrivePlayer::SheetDB - Sync the DrivePlayer library to/from a Google Sheet

=head1 SYNOPSIS

  use App::DrivePlayer::SheetDB;

  my $sheet = App::DrivePlayer::SheetDB->new(
      api            => $google_rest_api,
      spreadsheet_id => $id,             # omit when calling create()
  );

  my $id      = $sheet->create();             # create spreadsheet, returns ID
  my $summary = $sheet->sync_with_db($db, drive_exists => \&check);
  my $counts  = $sheet->push_to_sheet($db);   # merge-push only
  my $counts  = $sheet->pull_from_sheet($db); # new-device restore