- # 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;
+ # Get the list of all people in the sheet (we're going to need them soon anyway).
+ 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);
+
+ 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) . ")");
+ }
+ }
+
+ # Get our existing Slack->name mapping, from the sheets.
+ my %slack_userid_to_real_name = ();
+ my %slack_userid_to_slack_name = ();
+ my %slack_userid_to_row = ();
+ $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
+ );
+ my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
+ my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ my $cur_row = 5;
+ for my $row (@$mapping_sheet_rows) {
+ my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
+ my $slack_name = $row->{'values'}[1]{'userEnteredValue'}{'stringValue'};
+ my $real_name = get_spreadsheet_name($row->{'values'}[2]); # TODO support more
+ $slack_userid_to_row{$slack_id} = $cur_row++;
+ next if (!defined($slack_name));
+ $slack_userid_to_slack_name{$slack_id} = $slack_name;
+ next if (!defined($real_name));
+ $slack_userid_to_real_name{$slack_id} = $real_name;
+ }
+
+ # See which ones we don't have a mapping for, and look them up in Slack.
+ # TODO: Use an append call instead of $cur_row?
+ my @slack_mapping_updates = ();
+ for my $userid (@attending_userids) {
+ next if (exists($slack_userid_to_real_name{$userid}));
+
+ # Make sure they have a row in the spreadsheet.
+ my $write_row;
+ if (exists($slack_userid_to_row{$userid})) {
+ $write_row = $slack_userid_to_row{$userid};
+ } else {
+ $write_row = $cur_row++;
+ $slack_userid_to_row{$userid} = $write_row;
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!A$write_row:A$write_row",
+ values => [ [ $userid ]]
+ };
+ }
+
+ # Fetch their Slack name if we don't already have it.
+ my $slack_name;
+ if (exists($slack_userid_to_slack_name{$userid})) {
+ $slack_name = $slack_userid_to_slack_name{$userid};
+ } else {
+ $slack_userid_to_slack_name{$userid} = $slack_name;
+ $slack_name = get_slack_name($ua, $userid);
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!B$write_row:B$write_row",
+ values => [ [ $slack_name ]]
+ };
+ $slack_userid_to_slack_name{$userid} = $slack_name;
+ }
+
+ if (exists($seen_names{lc $slack_name})) {
+ # The name exists exactly, once or more, so it's a direct match and we ignore any fuzz.
+ $slack_userid_to_real_name{$userid} = $slack_name;
+ push @slack_mapping_updates, {
+ range => "Slack-mapping!C$write_row:C$write_row",
+ values => [ [ $slack_name ]]
+ };
+ } else {
+ # Do a search through all the available names in the sheet to find an obvious(ish) match.
+ my @candidates = ();
+ my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+ for my $row (@$main_sheet_rows) {
+ for my $val (@{$row->{'values'}}) {
+ my $name = get_spreadsheet_name($val);
+ if (defined($name) && matches_name($slack_name, $name)) {
+ push @candidates, $name;
+ }