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;
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);
+}
+
+# Make a mapping of lowercase name -> list of [canonical name, row number, column number]
+sub find_where_each_name_is {
+ my $json = shift;
+
+ my %seen_names = ();
+ my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $rowno = 4;
+ for my $row (@$rows) {
+ my $colno = 0;
+ for my $val (@{$row->{'values'}}) {
+ my $name = get_spreadsheet_name($val);
+ if (defined($name)) {
+ push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
+ }
+ ++$colno;
+ }
+ ++$rowno;
+ }
+
+ return %seen_names;
+}
+# Add the reaction log. (This only takes into account the last change
+# for each user; earlier ones are irrelevant and don't count. But it
+# doesn't deduplicate across reactions. Meh.)
+sub create_reaction_log {
+ my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
+
+ my $q = $dbh->prepare('select userid,event_type,reaction,to_char(event_ts,\'YYYY-mm-dd HH24:MI\') as event_ts from ( select distinct on (channel,ts,userid,reaction) userid,event_type,reaction,timestamptz \'1970-01-01 utc\' + event_ts::float * interval \'1 second\' as event_ts from reaction_log where channel=? and ts=? and reaction in (\'heart\',\'open_mouth\') order by channel,ts,userid,reaction,event_ts desc ) t1 where event_ts > current_timestamp - interval \'8 hours\' order by event_ts desc limit 50');
+ $q->execute($config::invitation_channel, $invitation_ts);
+ my @recent_changes = ();
+ while (my $ref = $q->fetchrow_hashref) {
+ my $msg = $ref->{'event_ts'};
+ if ($ref->{'event_type'} eq 'reaction_added') {
+ $msg .= ' +';
+ } else {
+ $msg .= ' –';
+ }
+ if ($ref->{'reaction'} eq 'open_mouth') {
+ $msg .= '😮';
+ } else {
+ $msg .= '❤️';
+ }
+ $msg .= ' ';
+ if (exists($slack_userid_to_real_name->{$ref->{'userid'}})) {
+ $msg .= $slack_userid_to_real_name->{$ref->{'userid'}};
+ } elsif (exists($slack_userid_to_slack_name->{$ref->{'userid'}})) {
+ $msg .= $slack_userid_to_slack_name->{$ref->{'userid'}} . ' (fant ikke regneark-navn)';
+ } else {
+ # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
+ $msg .= $ref->{'userid'} . ' (fant ikke Slack-navn)';
+ }
+ push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
+ }
+ while (scalar @recent_changes < 50) {
+ push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
+ }
+ return @recent_changes;
+}
+
skv_log("Siste sync startet: " . POSIX::ctime(time));
# Initialize the handles we need for communication.
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() ]);
}
# 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);
-# Duplicate detection
-my %seen_names = ();
-{
- my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
- my $rowno = 9;
- for my $row (@$main_sheet_rows) {
- my $colno = 0;
- for my $val (@{$row->{'values'}}) {
- my $name = get_spreadsheet_name($val);
- if (defined($name)) {
- push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
- }
- ++$colno;
- }
- ++$rowno;
- }
- for my $name (sort keys %seen_names) {
- my $seen = $seen_names{$name};
- if (scalar @$seen >= 2) {
- my $exemplar = $seen->[0][0];
- skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
- }
+my %seen_names = find_where_each_name_is($main_sheet_json);
+
+# Find duplicates.
+for my $name (sort keys %seen_names) {
+ my $seen = $seen_names{$name};
+ if (scalar @$seen >= 2) {
+ my $exemplar = $seen->[0][0];
+ skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
}
}
my %slack_userid_to_real_name = ();
my %slack_userid_to_slack_name = ();
my %slack_userid_to_row = ();
-my %real_name_to_slack_userid = ();
$response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue',
Authorization => 'Bearer ' . $token
);
$slack_userid_to_slack_name{$slack_id} = $slack_name;
next if (!defined($real_name));
$slack_userid_to_real_name{$slack_id} = $real_name;
- $real_name_to_slack_userid{$real_name} = $slack_id;
}
# See which ones we don't have a mapping for, and look them up in Slack.
# See if we can find them in the spreadsheet.
if (!exists($seen_names{lc $real_name})) {
+ # TODO: Perhaps move this logic further down, for consistency?
skv_log("$slack_name ($userid) er påmeldt på Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
} else {
my $seen = $seen_names{lc $real_name};
}
for my $real_name (keys %have_names) {
next if (exists($want_names{$real_name}));
- skv_log("Fjerner at $real_name skal på trening.");
- push @diffs, [
- $real_name,
- {
- backgroundColor => {
- red => 1,
- green => 1,
- blue => 1,
- alpha => 0
+ if (!exists($seen_names{lc $real_name})) {
+ # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
+ skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
+ } else {
+ skv_log("Fjerner at $real_name skal på trening.");
+ push @diffs, [
+ $real_name,
+ {
+ backgroundColor => {
+ red => 1,
+ green => 1,
+ blue => 1,
+ alpha => 0
+ }
}
- }
- ];
- $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
- $config::invitation_channel, $invitation_ts, $real_name);
+ ];
+ $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
+ $config::invitation_channel, $invitation_ts, $real_name);
+ }
}
my @yellow_updates = ();
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;
+ my $seen = $seen_names{lc $real_name};
+ die if (!defined($seen) || 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
}
- ++$colno;
}
- ++$rowno;
- }
-
- if ($found) {
- } else {
- skv_log("Ønsket å fjerne at $real_name skulle på trening, men de var ikke i regnearket lenger.");
- }
+ };
}
}
-# Add the reaction log. (This only takes into account the last change
-# for each user; earlier ones are irrelevant and don't count. But it
-# doesn't deduplicate across reactions. Meh.)
-$q = $dbh->prepare('select userid,event_type,reaction,to_char(event_ts,\'YYYY-mm-dd HH24:MI\') as event_ts from ( select distinct on (channel,ts,userid,reaction) userid,event_type,reaction,timestamptz \'1970-01-01 utc\' + event_ts::float * interval \'1 second\' as event_ts from reaction_log where channel=? and ts=? and reaction in (\'heart\',\'open_mouth\') order by channel,ts,userid,reaction,event_ts desc ) t1 where event_ts > current_timestamp - interval \'8 hours\' order by event_ts desc limit 50');
-$q->execute($config::invitation_channel, $invitation_ts);
-my @recent_changes = ();
-while (my $ref = $q->fetchrow_hashref) {
- my $msg = $ref->{'event_ts'};
- if ($ref->{'event_type'} eq 'reaction_added') {
- $msg .= ' +';
- } else {
- $msg .= ' –';
- }
- if ($ref->{'reaction'} eq 'open_mouth') {
- $msg .= '😮';
- } else {
- $msg .= '❤️';
- }
- $msg .= ' ';
- if (exists($slack_userid_to_real_name{$ref->{'userid'}})) {
- $msg .= $slack_userid_to_real_name{$ref->{'userid'}};
- } elsif (exists($slack_userid_to_slack_name{$ref->{'userid'}})) {
- $msg .= $slack_userid_to_slack_name{$ref->{'userid'}} . ' (fant ikke regneark-navn)';
- } else {
- # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
- $msg .= $ref->{'userid'} . ' (fant ikke Slack-navn)';
- }
- push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
-}
-while (scalar @recent_changes < 50) {
- push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
-}
+my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
push @yellow_updates, {
updateCells => {
rows => \@recent_changes,
projects / skvidarsync / blobdiff