Use seen_names for the final location.

[skvidarsync] / bin / sync.pl

diff --git a/bin/sync.pl b/bin/sync.pl
index 02f32952edca57db2b6d0a53e5eb6db12b70ed66..3f07537389ce496e1655b2df1e0104f6baa95256 100644 (file)
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -54,7 +54,7 @@ sub get_spreadsheet_name {
        my $cell = shift;
        my $name = $cell->{'userEnteredValue'}{'stringValue'};
        return undef if (!defined($name));
-       return undef if ($name =~ /^G[1-4]\.[1-4]/);
+       return undef if ($name =~ /^G[1-4]\.[1-5]/);
        $name =~ s/🆕//;
        $name =~ s/\(.*\)//g;
        $name =~ s/\[.*\]//g;
@@ -137,6 +137,26 @@ sub sheet_batch_update {
        die $response->decoded_content if !$response->is_success;
 }
 
+sub get_spreadsheet_with_title {
+       my ($ua, $token, $wanted_sheet_title) = @_;
+
+       # See if we have any spreadsheets that match this title.
+       my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
+               Authorization => 'Bearer ' . $token
+       );
+       my $sheets_json = JSON::XS::decode_json($response->decoded_content);
+       my ($tab_name, $tab_id);
+       for my $sheet (@{$sheets_json->{'sheets'}}) {
+               my $title = $sheet->{'properties'}{'title'};
+               my $sheet_id = $sheet->{'properties'}{'sheetId'};
+               if ($title =~ /\Q$wanted_sheet_title\E/) {
+                       # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket “$title” (fane-ID $sheet_id).");
+                       return ($title, $sheet_id);
+               }
+       }
+       return (undef, undef);
+}
+
 skv_log("Siste sync startet: " . POSIX::ctime(time));
 
 # Initialize the handles we need for communication.
@@ -154,22 +174,7 @@ my $invitation_ts = $linkref->{'ts'};
 my $wanted_sheet_title = $linkref->{'sheet_title'};
 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
 
-# See if we have any spreadsheets that match this title.
-my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
-       Authorization => 'Bearer ' . $token
-);
-my $sheets_json = JSON::XS::decode_json($response->decoded_content);
-my ($tab_name, $tab_id);
-for my $sheet (@{$sheets_json->{'sheets'}}) {
-       my $title = $sheet->{'properties'}{'title'};
-       my $sheet_id = $sheet->{'properties'}{'sheetId'};
-       if ($title =~ /\Q$wanted_sheet_title\E/) {
-               # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket “$title” (fane-ID $sheet_id).");
-               $tab_name = $title;
-               $tab_id = $sheet_id;
-               last;
-       }
-}
+my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
 if (!defined($tab_name)) {
        skv_log("Fant ikke noen fane med “$wanted_sheet_title” i navnet; kan ikke synkronisere.\n");
        sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
@@ -185,7 +190,7 @@ while (my $ref = $q->fetchrow_hashref) {
 }
 
 # Get the list of all people in the sheet (we're going to need them soon anyway).
-$response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A9:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
+my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&fields=sheets/data/rowData/values/userEnteredValue',
        Authorization => 'Bearer ' . $token
 );
 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
@@ -194,7 +199,7 @@ my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
 my %seen_names = ();
 {
        my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
-       my $rowno = 9;
+       my $rowno = 4;
        for my $row (@$main_sheet_rows) {
                my $colno = 0;
                for my $val (@{$row->{'values'}}) {
@@ -391,41 +396,32 @@ if (scalar @diffs > 0) {
                my $real_name = $diff->[0];
 
                # See if we can find them in the spreadsheet.
-               # FIXME use seen_names
-               my $rowno = 8;  # zero-indexed
-               my $found = 0;
-               for my $row (@$main_sheet_rows) {
-                       my $colno = 0;
-                       for my $val (@{$row->{'values'}}) {
-                               my $name = get_spreadsheet_name($val);
-                               if (defined($name) && $real_name eq $name) {
-                                       push @yellow_updates, {
-                                               updateCells => {
-                                                       rows => [{
-                                                               values => [{
-                                                                       userEnteredFormat => $diff->[1]
-                                                               }]
-                                                       }],
-                                                       fields => 'userEnteredFormat.backgroundColor',
-                                                       range => {
-                                                               sheetId => $tab_id,
-                                                               startRowIndex => $rowno,
-                                                               endRowIndex => $rowno + 1,
-                                                               startColumnIndex => $colno,
-                                                               endColumnIndex => $colno + 1
-                                                       }
-                                               }
-                                       };
-                                       $found = 1;
-                               }
-                               ++$colno;
-                       }
-                       ++$rowno;
-               }
-
-               if ($found) {
-               } else {
+               if (!exists($seen_names{lc $real_name})) {
+                       # This can only happen on deletes; we don't add such cases to the positive diff.
+                       # FIXME we'd still delete them from the database.
                        skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
+               } else {
+                       my $seen = $seen_names{lc $real_name};
+                       die if (scalar @$seen > 1);
+                       my $rowno = $seen->[0][1];
+                       my $colno = $seen->[0][2];
+                       push @yellow_updates, {
+                               updateCells => {
+                                       rows => [{
+                                               values => [{
+                                                       userEnteredFormat => $diff->[1]
+                                               }]
+                                       }],
+                                       fields => 'userEnteredFormat.backgroundColor',
+                                       range => {
+                                               sheetId => $tab_id,
+                                               startRowIndex => $rowno,
+                                               endRowIndex => $rowno + 1,
+                                               startColumnIndex => $colno,
+                                               endColumnIndex => $colno + 1
+                                       }
+                               }
+                       };
                }
        }
 }