From 1812e8f7e2f1271aad733c93643f4448c0a3eb91 Mon Sep 17 00:00:00 2001
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
Date: Fri, 10 Nov 2023 18:05:13 +0100
Subject: [PATCH] Combine the two Sheets API calls into one, to reduce the
 latency further.

---
 bin/sync.pl | 28 ++++++++++++----------------
 1 file changed, 12 insertions(+), 16 deletions(-)

diff --git a/bin/sync.pl b/bin/sync.pl
index e913c16..bfac355 100644
--- a/bin/sync.pl
+++ b/bin/sync.pl
@@ -189,7 +189,7 @@ sub get_group_assignments {
 	my $json = shift;
 
 	my %assignments = ();
-	my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+	my $rows = $json->{'data'}[0]{'rowData'};
 	my @curr_groups = ();
 	for my $row (@$rows) {
 		my $col = 0;
@@ -280,7 +280,7 @@ sub find_where_each_name_is {
 	my $json = shift;
 
 	my %seen_names = ();
-	my $rows = $json->{'sheets'}[0]{'data'}[0]{'rowData'};
+	my $rows = $json->{'data'}[0]{'rowData'};
 	my $rowno = 3;
 	for my $row (@$rows) {
 		my $colno = 0;
@@ -540,15 +540,18 @@ sub run {
 		delete $colors{$userid};
 	}
 
-	# Get the list of all people in the sheet (we're going to need them soon anyway).
+	# Get the list of all people in the sheet (we're going to need them soon).
+	# Also get the Slack mapping when we're doing an API request anyway.
 	my $start = [Time::HiRes::gettimeofday];
-	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',
+	my $response = $ua->get('https://sheets.googleapis.com/v4/spreadsheets/' . $config::sheet_id . '?key=' . $config::gsheets_api_key . '&ranges=' . $tab_name . '!A4:Z5000&ranges=Slack-mapping!A5:C5000&fields=sheets/data/rowData/values/userEnteredValue',
 		Authorization => 'Bearer ' . $token,
 		Accept_Encoding => HTTP::Message::decodable
 	);
-	log_timing($start, "/spreadsheets/$tab_name");
+	log_timing($start, "/spreadsheets/");
 
-	my $main_sheet_json = JSON::XS::decode_json($response->decoded_content);
+	my $sheets_json = JSON::XS::decode_json($response->decoded_content);
+	my $main_sheet_json = $sheets_json->{'sheets'}[0];
+	my $mapping_sheet_json = $sheets_json->{'sheets'}[1];
 
 	# Update the list of groups we've seen people in.
 	my %assignments = get_group_assignments($main_sheet_json);
@@ -570,14 +573,7 @@ sub run {
 	my %slack_userid_to_slack_name = ();
 	my %slack_userid_to_row = ();
 
-	$start = [Time::HiRes::gettimeofday];
-	$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,
-		Accept_Encoding => HTTP::Message::decodable
-	);
-	log_timing($start, "/spreadsheets/Slack-mapping");
-	my $mapping_sheet_json = JSON::XS::decode_json($response->decoded_content);
-	my $mapping_sheet_rows = $mapping_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+	my $mapping_sheet_rows = $mapping_sheet_json->{'data'}[0]{'rowData'};
 	my $cur_row = 5;
 	for my $row (@$mapping_sheet_rows) {
 		my $slack_id = $row->{'values'}[0]{'userEnteredValue'}{'stringValue'};
@@ -633,7 +629,7 @@ sub run {
 		} 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'};
+			my $main_sheet_rows = $main_sheet_json->{'data'}[0]{'rowData'};
 			for my $row (@$main_sheet_rows) {
 				for my $val (@{$row->{'values'}}) {
 					my $name = get_spreadsheet_name($val);
@@ -690,7 +686,7 @@ sub run {
 
 	# Find the list of names to mark yellow.
 	my %want_colors = ();
-	my $main_sheet_rows = $main_sheet_json->{'sheets'}[0]{'data'}[0]{'rowData'};
+	my $main_sheet_rows = $main_sheet_json->{'data'}[0]{'rowData'};
 	for my $userid (@attending_userids) {
 		next if (!exists($slack_userid_to_real_name{$userid}));
 		my $slack_name = $slack_userid_to_slack_name{$userid};
-- 
2.39.2