11 binmode STDOUT, ':utf8';
12 binmode STDERR, ':utf8';
16 # - make the sheet message more in-your-face (ephemeral message)
18 require '../include/config.pm';
44 sub get_oauth_bearer_token {
47 my $jwt = JSON::XS::encode_json({
48 "iss" => $config::jwt_key->{'client_email'},
49 "scope" => "https://www.googleapis.com/auth/spreadsheets",
50 "aud" => "https://www.googleapis.com/oauth2/v4/token",
54 my $jws_token = Crypt::JWT::encode_jwt(payload=>$jwt, alg=>'RS256', key=>\$config::jwt_key->{'private_key'});
55 my $response = $ua->post('https://www.googleapis.com/oauth2/v4/token', [
56 'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
57 'assertion' => $jws_token ]);
58 return JSON::XS::decode_json($response->decoded_content)->{'access_token'};
62 my ($ua, $userid) = @_;
63 my $req = HTTP::Request->new('GET', 'https://slack.com/api/users.info?user=' . $userid, [
64 'Authorization' => 'Bearer ' . $config::slack_oauth_token
66 my $response = $ua->request($req);
67 die $response->status_line if !$response->is_success;
69 my $user_json = JSON::XS::decode_json($response->decoded_content);
70 die "Something went wrong: " . $response->decoded_content if (!defined($user_json) || !$user_json->{'ok'});
72 return $user_json->{'user'}{'real_name'};
75 sub get_spreadsheet_name {
77 my $name = $cell->{'userEnteredValue'}{'stringValue'};
78 return undef if (!defined($name));
79 return undef if ($name =~ /^G[1-4]\.[1-5]/);
84 $name =~ s/G\d\.\d?\??//;
92 my ($slack_name, $spreadsheet_name) = @_;
93 if (lc($slack_name) eq lc($spreadsheet_name)) {
97 my @ap = split /\s+/, $slack_name;
98 my @bp = split /\s+/, $spreadsheet_name;
99 if (scalar @ap >= 2 && scalar @bp >= 2 && lc($ap[0]) eq lc($bp[0])) {
100 # First name matches, try to match some surname
102 for my $ai (1..$#ap) {
103 for my $bi (1..$#bp) {
104 $found = 1 if (lc($ap[$ai]) eq lc($bp[$bi]));
108 skv_log("Fuzzy-matchet $slack_name -> $spreadsheet_name.");
116 sub format_cell_names_for_seen {
118 my @cells = map { chr(ord('A') + $_->[2]) . ($_->[1] + 1) } @$seen;
119 return join(', ', @cells);
124 print STDERR "$msg\n";
128 sub serialize_skv_log_to_sheet {
133 userEnteredValue => { stringValue => join("\n", @log) }
136 fields => 'userEnteredValue.stringValue',
138 sheetId => $config::log_tab_id,
141 startColumnIndex => 0,
148 sub sheet_batch_update {
149 my ($ua, $token, @requests) = @_;
151 requests => \@requests
153 my $response = $ua->post(
154 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . ':batchUpdate?key=' . $config::gsheets_api_key,
155 Content => JSON::XS::encode_json($update),
156 Content_type => 'application/json;charset=UTF-8',
157 Authorization => 'Bearer ' . $token
159 die $response->decoded_content if !$response->is_success;
162 sub get_group_assignments {
165 my %assignments = ();
166 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
167 my @curr_groups = ();
168 for my $row (@$rows) {
170 for my $val (@{$row->{'values'}}) {
172 my $contents = $val->{'userEnteredValue'}{'stringValue'};
173 next if !defined($contents);
174 if ($contents =~ /Gruppe /) {
178 next if $contents =~ /^VL:/;
179 next if $contents =~ /^LT\b/;
180 next if $contents =~ /^400m/;
181 next if $contents =~ /^546m/;
182 if ($contents =~ /^(G\d\.\d)/ || $contents =~ /^(Nye lĆøpere.*)/) {
183 $curr_groups[$col] = $1;
185 my $name = get_spreadsheet_name($val);
186 next if (!defined($name));
187 my $group = $curr_groups[$col] // $curr_groups[$col - 1];
188 # print $group, " ", $name, "\n";
189 if (exists($assignments{$name})) {
190 $assignments{$name} = "(flere grupper)";
192 $assignments{$name} = $group;
200 sub update_assignment_db {
201 my ($dbh, $channel, $ts, $assignments) = @_;
203 local $dbh->{AutoCommit} = 0;
204 my %db_assignments = ();
205 my $q = $dbh->prepare('SELECT name,group_name FROM current_group_membership_history WHERE channel=? AND ts=?');
206 $q->execute($channel, $ts);
207 while (my $ref = $q->fetchrow_hashref) {
208 if (defined($ref->{'group_name'})) {
209 $db_assignments{$ref->{'name'}} = $ref->{'group_name'};
213 $q = $dbh->prepare('INSERT INTO group_membership_history (channel, ts, name, change_seen, group_name) VALUES (?, ?, ?, CURRENT_TIMESTAMP, ?)');
214 for my $name (keys %$assignments) {
215 if (!exists($db_assignments{$name}) || $db_assignments{$name} ne $assignments->{$name}) {
216 $q->execute($channel, $ts, $name, $assignments->{$name});
219 for my $name (keys %db_assignments) {
220 if (!exists($assignments->{$name})) {
221 $q->execute($channel, $ts, $name, undef);
227 sub get_spreadsheet_with_title {
228 my ($ua, $token, $wanted_sheet_title) = @_;
230 # See if we have any spreadsheets that match this title.
231 my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&fields=sheets/properties',
232 Authorization => 'Bearer ' . $token
234 my $sheets_json = JSON::XS::decode_json($response->decoded_content);
235 my ($tab_name, $tab_id);
236 for my $sheet (@{$sheets_json->{'sheets'}}) {
237 my $title = $sheet->{'properties'}{'title'};
238 my $sheet_id = $sheet->{'properties'}{'sheetId'};
239 if ($title =~ /\Q$wanted_sheet_title\E/) {
240 # skv_log("Synkroniserer ($config::invitation_channel, $invitation_ts) mot arket ā$titleā (fane-ID $sheet_id).");
241 return ($title, $sheet_id);
244 return (undef, undef);
247 # Make a mapping of lowercase name -> list of [canonical name, row number, column number]
248 sub find_where_each_name_is {
252 my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
254 for my $row (@$rows) {
256 for my $val (@{$row->{'values'}}) {
257 my $name = get_spreadsheet_name($val);
258 if (defined($name)) {
259 push @{$seen_names{lc $name}}, [$name, $rowno, $colno];
269 sub best_name_for_log {
270 my ($userid, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
271 if (exists($slack_userid_to_real_name->{$userid})) {
272 return $slack_userid_to_real_name->{$userid};
273 } elsif (exists($slack_userid_to_slack_name->{$userid})) {
274 return $slack_userid_to_slack_name->{$userid} . ' (fant ikke regneark-navn)';
276 # Should only happen if we didn't see the initial reaction_add, only reaction_remove.
277 # (TODO: Is the comment above true anymore, now that we use this from multiple contexts?)
278 return $userid . ' (fant ikke Slack-navn)';
282 # Add the reaction log. (This only takes into account the last change
283 # for each user; earlier ones are irrelevant and don't count. But it
284 # doesn't deduplicate across reactions. Meh.)
285 sub create_reaction_log {
286 my ($dbh, $invitation_ts, $slack_userid_to_real_name, $slack_userid_to_slack_name) = @_;
288 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\',\'blue_heart\') 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');
289 $q->execute($config::invitation_channel, $invitation_ts);
290 my @recent_changes = ();
291 while (my $ref = $q->fetchrow_hashref) {
292 my $msg = $ref->{'event_ts'};
293 if ($ref->{'event_type'} eq 'reaction_added') {
298 if ($ref->{'reaction'} eq 'open_mouth') {
300 } elsif ($ref->{'reaction'} eq 'blue_heart') {
306 $msg .= best_name_for_log($ref->{'userid'}, $slack_userid_to_real_name, $slack_userid_to_slack_name);
307 push @recent_changes, { values => [{ userEnteredValue => { stringValue => $msg } }] };
309 while (scalar @recent_changes < 50) {
310 push @recent_changes, { values => [{ userEnteredValue => { stringValue => '' } }] };
312 return @recent_changes;
315 sub create_move_log {
316 my ($dbh, $invitation_ts, $prev_invitation_ts) = @_;
317 my $q = $dbh->prepare(<<"EOF");
319 name, g_old.group_name as old_group, g_new.group_name as new_group, TO_CHAR(g_new.change_seen, \'YYYY-mm-dd HH24:MI\') AS change_seen
320 FROM ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_old
321 FULL OUTER JOIN ( SELECT * FROM current_group_membership_history WHERE ts=? ) g_new USING (name)
323 g_new.group_name IS DISTINCT FROM g_old.group_name
324 AND g_new.group_name IS NOT NULL
325 ORDER BY g_new.change_seen DESC, name
328 $q->execute($prev_invitation_ts, $invitation_ts);
329 my @recent_moves = ();
330 while (my $ref = $q->fetchrow_hashref) {
331 my $name = $ref->{'name'};
332 my $old_group = $ref->{'old_group'};
333 my $new_group = $ref->{'new_group'};
335 my $msg = $ref->{'change_seen'} . " ";
336 if (!defined($old_group)) {
337 $msg .= "$name, (ny lĆøper) ā $new_group";
339 $msg .= "$name, $old_group ā $new_group";
341 push @recent_moves, { values => [{ userEnteredValue => { stringValue => $msg } }] };
343 while (scalar @recent_moves < 50) {
344 push @recent_moves, { values => [{ userEnteredValue => { stringValue => '' } }] };
346 return @recent_moves;
349 # Also applies the diff to the database (a bit ugly).
351 my ($dbh, $invitation_ts, $want_colors, $have_colors, $seen_names) = @_;
354 for my $real_name (keys %$want_colors) {
355 my $wc = $want_colors->{$real_name};
356 if (exists($have_colors->{$real_name})) {
357 if ($have_colors->{$real_name} eq $wc) {
361 skv_log("Markerer at $real_name har byttet treningssted.");
363 $real_name, { backgroundColor => $rgb{$wc} }
365 $dbh->do('UPDATE applied SET color=? WHERE channel=? AND ts=? AND name=?', undef,
366 $wc, $config::invitation_channel, $invitation_ts, $real_name);
368 skv_log("Markerer at $real_name skal pƄ trening.");
370 $real_name, { backgroundColor => $rgb{$wc} }
372 $dbh->do('INSERT INTO applied (channel, ts, name, color) VALUES (?, ?, ?, ?)', undef,
373 $config::invitation_channel, $invitation_ts, $real_name, $wc);
376 for my $real_name (keys %$have_colors) {
377 next if (exists($want_colors->{$real_name}));
378 if (!exists($seen_names->{lc $real_name})) {
379 # TODO: This can somehow come if we try to add someone who's not in the sheet, too?
380 skv_log("Ćnsket Ć„ fjerne at $real_name skulle pĆ„ trening, men de var ikke i regnearket lenger.");
381 } elsif (scalar @{$seen_names->{lc $real_name}} > 1) {
384 skv_log("Fjerner at $real_name skal pƄ trening.");
386 $real_name, { backgroundColor => $rgb{white} }
388 $dbh->do('DELETE FROM applied WHERE channel=? AND ts=? AND name=?', undef,
389 $config::invitation_channel, $invitation_ts, $real_name);
395 sub possibly_nag_user {
396 my ($dbh, $ua, $userid) = @_;
398 # See if we've nagged this user before.
399 my $q = $dbh->prepare('SELECT * FROM users_nagged WHERE userid=?');
400 $q->execute($userid);
401 if (defined($q->fetchrow_hashref)) {
405 skv_log("Sender melding til $userid for Ć„ spĆørre om gruppe.");
406 my $response = $ua->post(
407 'https://slack.com/api/conversations.open',
408 Content => JSON::XS::encode_json({ users => [ $userid ] }),
409 Content_type => 'application/json;charset=UTF-8',
410 Authorization => 'Bearer ' . $config::slack_oauth_token
412 die $response->status_line if !$response->is_success;
414 my $im_json = JSON::XS::decode_json($response->decoded_content);
415 die "Something went wrong: " . $response->decoded_content if (!defined($im_json) || !$im_json->{'ok'});
416 my $channel_id = $im_json->{'channel'}{'id'};
418 my $msg = "Hei! Du meldte deg akkurat pĆ„ trening, men vi klarer ikke Ć„ finne deg i en gruppe i regnearket. For at det skal vƦre enklere for trenerne, Ćønsker vi gjerne at du gĆ„r inn pĆ„ https://regneark.skvidar.run/ og skriver deg inn der med samme navn som du bruker pĆ„ Slack. Om du er usikker pĆ„ hvilken gruppe som passer for deg, ta gjerne kontakt med en trener. Velkommen pĆ„ trening og til klubben!";
420 $response = $ua->post(
421 'https://slack.com/api/chat.postMessage',
422 Content => JSON::XS::encode_json({ channel => $channel_id, text => $msg }),
423 Content_type => 'application/json;charset=UTF-8',
424 Authorization => 'Bearer ' . $config::slack_oauth_token
426 my $msg_json = JSON::XS::decode_json($response->decoded_content);
427 die "Something went wrong: " . $response->decoded_content if (!defined($msg_json) || !$msg_json->{'ok'});
429 # Mark that we've sent the message, so it won't happen again.
430 $dbh->do('INSERT INTO users_nagged (userid, last_nag) VALUES (?, CURRENT_TIMESTAMP)', undef, $userid);
434 my $start = [Time::HiRes::gettimeofday];
437 skv_log("Siste sync startet: " . POSIX::ctime(time));
439 # Initialize the handles we need for communication.
440 $dbh = DBI->connect("dbi:Pg:dbname=$config::dbname;host=127.0.0.1", $config::dbuser, $config::dbpass, {RaiseError => 1})
441 or die "Could not connect to Postgres: " . DBI->errstr;
442 my $ua = LWP::UserAgent->new('SKVidarLang/1.0');
443 my $token = get_oauth_bearer_token($ua);
445 # Find the newest message, what it is linked to, and what was the one before it (for group diffing).
446 # TODO: Support more than one, and test better for errors here.
447 my $q = $dbh->prepare('select * from message_sheet_link where channel=? order by ts desc limit 2');
448 $q->execute($config::invitation_channel);
449 my $linkref = $q->fetchrow_hashref;
450 my $invitation_ts = $linkref->{'ts'};
451 my $wanted_sheet_title = $linkref->{'sheet_title'};
452 die "Could not get newest sheet title" if (!defined($wanted_sheet_title));
454 my ($tab_name, $tab_id) = get_spreadsheet_with_title($ua, $token, $wanted_sheet_title);
455 if (!defined($tab_name)) {
456 skv_log("Fant ikke noen fane med Ā«$wanted_sheet_titleĀ» i navnet; kan ikke synkronisere.\n");
457 sheet_batch_update($ua, $token, [ serialize_skv_log_to_sheet() ]);
461 # Store away the second-newest ID.
462 my $prev_invitation_ts = $q->fetchrow_hashref->{'ts'};
464 # Find everyone who are marked as attending on Slack (via reactions).
465 $q = $dbh->prepare('SELECT DISTINCT userid,reaction FROM current_reactions WHERE channel=? AND ts=? AND reaction IN (\'heart\', \'open_mouth\', \'blue_heart\')');
466 $q->execute($config::invitation_channel, $invitation_ts);
467 my @attending_userids = ();
470 while (my $ref = $q->fetchrow_hashref) {
471 my $userid = $ref->{'userid'};
472 push @attending_userids, $userid;
473 if ($ref->{'reaction'} eq 'blue_heart') {
474 if (exists($colors{$userid}) && $colors{$userid} eq 'yellow') {
475 $double{$userid} = 1;
477 $colors{$userid} = 'blue';
479 if (exists($colors{$userid}) && $colors{$userid} eq 'blue') {
480 $double{$userid} = 1;
482 $colors{$userid} = 'yellow';
486 # Remove double-attenders (we will log them as warnings further down).
487 @attending_userids = grep { !exists($double{$_}) } @attending_userids;
488 for my $userid (keys %double) {
489 delete $colors{$userid};
492 # Get the list of all people in the sheet (we're going to need them soon anyway).
493 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',
494 Authorization => 'Bearer ' . $token
496 my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
498 # Update the list of groups we've seen people in.
499 my %assignments = get_group_assignments($main_sheet_json);
500 update_assignment_db($dbh, $config::invitation_channel, $invitation_ts, \%assignments);
502 my %seen_names = find_where_each_name_is($main_sheet_json);
505 for my $name (sort keys %seen_names) {
506 my $seen = $seen_names{$name};
507 if (scalar @$seen >= 2) {
508 my $exemplar = $seen->[0][0];
509 skv_log("Duplikat: $exemplar (" . format_cell_names_for_seen($seen) . ")");
513 # Get our existing Slack->name mapping, from the sheets.
514 my %slack_userid_to_real_name = ();
515 my %slack_userid_to_slack_name = ();
516 my %slack_userid_to_row = ();
517 $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',
518 Authorization => 'Bearer ' . $token
520 my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
521 my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
523 for my $row (@$mapping_sheet_rows) {
524 my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
525 my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
526 my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
527 $slack_userid_to_row{$slack_id} = $cur_row++;
528 next if (!defined($slack_name));
529 $slack_userid_to_slack_name{$slack_id} = $slack_name;
530 next if (!defined($real_name));
531 $slack_userid_to_real_name{$slack_id} = $real_name;
534 # See which ones we don't have a mapping for, and look them up in Slack.
535 # TODO: Use an append call instead of $cur_row?
536 my @slack_mapping_updates = ();
537 for my $userid (@attending_userids) {
538 next if (exists($slack_userid_to_real_name{$userid}));
540 # Make sure they have a row in the spreadsheet.
542 if (exists($slack_userid_to_row{$userid})) {
543 $write_row = $slack_userid_to_row{$userid};
545 $write_row = $cur_row++;
546 $slack_userid_to_row{$userid} = $write_row;
547 push @slack_mapping_updates, {
548 range => "Slack-mapping!A$write_row:A$write_row",
549 values => [ [ $userid ]]
553 # Fetch their Slack name if we don't already have it.
555 if (exists($slack_userid_to_slack_name{$userid})) {
556 $slack_name = $slack_userid_to_slack_name{$userid};
558 $slack_userid_to_slack_name{$userid} = $slack_name;
559 $slack_name = get_slack_name($ua, $userid);
560 push @slack_mapping_updates, {
561 range => "Slack-mapping!B$write_row:B$write_row",
562 values => [ [ $slack_name ]]
564 $slack_userid_to_slack_name{$userid} = $slack_name;
567 if (exists($seen_names{lc $slack_name})) {
568 # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
569 $slack_userid_to_real_name{$userid} = $slack_name;
570 push @slack_mapping_updates, {
571 range => "Slack-mapping!C$write_row:C$write_row",
572 values => [ [ $slack_name ]]
575 # Do a search through all the available names in the sheet to find an obvious(ish) match.
577 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
578 for my $row (@$main_sheet_rows) {
579 for my $val (@{$row->{'values'}}) {
580 my $name = get_spreadsheet_name($val);
581 if (defined($name) && matches_name($slack_name, $name)) {
582 push @candidates, $name;
586 if ($#candidates == -1) {
587 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men fant ikke et regneark-navn for dem.");
588 possibly_nag_user($dbh, $ua, $userid);
589 } elsif ($#candidates == 0) {
590 my $name = $candidates[0];
591 $slack_userid_to_real_name{$userid} = $name;
592 push @slack_mapping_updates, {
593 range => "Slack-mapping!C$write_row:C$write_row",
594 values => [ [ $name ]]
597 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men hadde flere fuzzy-matcher; vet ikke hvilket regneark-navn som skal brukes.");
602 valueInputOption => 'USER_ENTERED',
603 data => \@slack_mapping_updates
605 $response = $ua->post(
606 'https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '/values:batchUpdate?key=' . $config::gsheets_api_key,
607 Content => JSON::XS::encode_json($update),
608 Content_type => 'application/json;charset=UTF-8',
609 Authorization => 'Bearer ' . $token
611 die $response->decoded_content if (!$response->is_success);
613 # Now that we have Slack names, we can log double-reacters.
614 for my $userid (keys %double) {
615 my $name = best_name_for_log($userid, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
616 skv_log("$name er pƄmeldt flere steder pƄ Slack; vet ikke hvilken som skal brukes.");
619 # Find the list of names to mark yellow.
620 my %want_colors = ();
621 my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
622 for my $userid (@attending_userids) {
623 next if (!exists($slack_userid_to_real_name{$userid}));
624 my $slack_name = $slack_userid_to_slack_name{$userid};
625 my $real_name = $slack_userid_to_real_name{$userid};
627 # See if we can find them in the spreadsheet.
628 if (!exists($seen_names{lc $real_name})) {
629 # TODO: Perhaps move this logic further down, for consistency?
630 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, og er mappet til $real_name, men var ikke i noen gruppe.");
632 my $seen = $seen_names{lc $real_name};
633 if (scalar @$seen >= 2) {
634 skv_log("$slack_name ($userid) er pƄmeldt pƄ Slack, men stƄr flere steder (se over); vet ikke hvilken celle som skal brukes.");
636 $want_colors{$seen->[0][0]} = $colors{$userid};
641 # Find the list of names we already marked yellow.
642 my %have_colors = ();
643 $dbh->{AutoCommit} = 0;
644 $dbh->do('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
645 $q = $dbh->prepare('SELECT name,color FROM applied WHERE channel=? AND ts=?');
646 $q->execute($config::invitation_channel, $invitation_ts);
647 while (my $ref = $q->fetchrow_hashref) {
648 $have_colors{$ref->{'name'}} = $ref->{'color'};
651 my @diffs = find_diff($dbh, $invitation_ts, \%want_colors, \%have_colors, \%seen_names);
653 my @yellow_updates = ();
654 if (scalar @diffs > 0) {
655 # Now fill in the actual stuff.
656 for my $diff (@diffs) {
657 my $real_name = $diff->[0];
659 my $seen = $seen_names{lc $real_name};
661 # We've already complained about these earlier, so just skip them silently.
662 next if (scalar @$seen > 1);
664 # See if we can find them in the spreadsheet.
665 die "Could not find $real_name" if (!defined($seen));
666 my $rowno = $seen->[0][1];
667 my $colno = $seen->[0][2];
668 push @yellow_updates, {
672 userEnteredFormat => $diff->[1]
675 fields => 'userEnteredFormat.backgroundColor',
678 startRowIndex => $rowno,
679 endRowIndex => $rowno + 1,
680 startColumnIndex => $colno,
681 endColumnIndex => $colno + 1
688 my @recent_changes = create_reaction_log($dbh, $invitation_ts, \%slack_userid_to_real_name, \%slack_userid_to_slack_name);
689 push @yellow_updates, {
691 rows => \@recent_changes,
692 fields => 'userEnteredValue.stringValue',
694 sheetId => $config::log_tab_id,
696 endRowIndex => 4 + scalar @recent_changes,
697 startColumnIndex => 0,
703 my @recent_moves = create_move_log($dbh, $invitation_ts, $prev_invitation_ts);
704 push @yellow_updates, {
706 rows => \@recent_moves,
707 fields => 'userEnteredValue.stringValue',
709 sheetId => $config::log_tab_id,
711 endRowIndex => 4 + scalar @recent_moves,
712 startColumnIndex => 1,
718 # Push the final set of updates (including the log).
720 push @yellow_updates, serialize_skv_log_to_sheet();
721 sheet_batch_update($ua, $token, \@yellow_updates);
724 my $elapsed = Time::HiRes::tv_interval($start);
725 printf "Tok %.0f ms.\n", 1e3 * $elapsed;
728 if ($#ARGV >= 0 && $ARGV[0] eq '--daemon') {
729 # Start with a single, forced run.
730 unlink("/srv/skvidar-slack.sesse.net/marker");
734 if (!unlink("/srv/skvidar-slack.sesse.net/marker")) {
735 unless ($!{ENOENT}) {
736 warn "/srv/skvidar-slack.sesse.net/marker: $!";
745 warn "Died with: $@";