Writing up the issue

... snip ...

    my $credsfile = 'credentials.json';

    # If we mounted in as a directory with Docker, use that path
    if ((-d "/creds") && (-e "/creds/credentials.json")) {
            $credsfile = '/creds/credentials.json';
    }

    # If they passed in a credential, we will use that first
    if (exists $args->{credential}) {
        my $randomint = int(rand(10000));
        my $etime = time();

        my $temp_cred_file = "/tmp/mcp_google_cred_$etime$randomint.json";
        open(my $fh, '>', $temp_cred_file) or die "Could not open file '$temp_cred_file' $!";
        print $fh $args->{credential};
        close $fh;
        $credsfile = $temp_cred_file;
        print "Using temporary credential file at $temp_cred_file\n" if ($DEBUGMODE > 0);
    } else {
        # Check on Password if required, does not apply if using custom credential
        if ($USAGEPASS ne 'nopassword'){
            if (exists $args->{password}) {
                if ($args->{password} ne $USAGEPASS) {
                    print "Invalid password provided. Rejecting request.\n";
                    return "Invalid password provided for fetch_google_sheet tool.\n";
                } else {
                    print "Password accepted.\n";
                }
            } else {
                print "No password provided. Rejecting request.\n";
                return "No password provided for fetch_google_sheet tool.\n";
            }
        } else {
            print "No usage password set, skipping password check.\n";
        }
    }
    
    my @scopes = ('https://www.googleapis.com/auth/spreadsheets',
                  'https://www.googleapis.com/auth/drive');
    my $auth =  WWW::Google::Cloud::Auth::ServiceAccount->new(
        credentials_path => $credsfile,
        scope => join " ", @scopes
    );

    my $c = shift;
    my $token = $auth->get_token();
    print "token: $token\n" if ($DEBUGMODE > 0);
    my $sheet_id = $args->{sheetid};
    my $range = "A:Z"; # default range
    if (exists $args->{range}) {
        $range = $args->{range};
    }
    my ($return_code, $contents) = 
        read_spreadsheet_search($token, $sheet_id, $range, $args->{searchfield}, $args->{searchvalue});

    # we have no need for a custom credential file anymore
    if (exists $args->{credential}) {
        unlink $credsfile;
        print "Deleted temporary credential file at $credsfile\n" if ($DEBUGMODE > 0);
    }   
    
... snip ...

    my $randomint = int(rand(10000));
    my $etime = time();

    if (exists $args->{credential}) {
        unlink $credsfile;
        print "Deleted temporary credential file at $credsfile\n" if ($DEBUGMODE > 0);
    }   

use Data::Dumper;
use HTTP::Request;
use JSON;
use LWP::UserAgent;
use MCP::Server;
use Mojolicious::Lite -signatures;
use LWP::Protocol::https;
use URI::Encode;
use URI;
use WWW::Google::Cloud::Auth::ServiceAccount;
use experimental qw(declared_refs refaliasing signatures);
use feature qw(say);
use strict;
use warnings;

my $server = MCP::Server->new;
my $USAGEPASS = $ENV{'MCP_USAGE_PASSWORD'} || 'nopassword';
my $DEBUGMODE = $ENV{'MCP_DEBUG'} + 0;


# Tool: append_google_sheet_row
# Description: Used to append a row to a google sheet
$server->tool(
  name         => 'append_google_sheet_row',
  description  => 'Append a row to a google sheet. You can specify the range, e.g. A1:Z378, and appendtype (overwrite or append_rows), default is append_rows. Range is optional (default A:Z).',
  input_schema => {type => 'object', properties => {sheetid => {type => 'string'}, password => {type => 'string'}, credential => {type => 'string'}, range => {type => 'string'}, appendtype => {type => 'string'}, values => {type => 'array'}}, required => ['sheetid','values']},
  code         => sub ($tool, $args) {
    my ($auth, $credsfile, $error) = get_authenticated_service_account($args, 'append_google_sheet_row');
    return $error if $error;

    my $token = $auth->get_token();
    print "token: $token\n" if ($DEBUGMODE > 0);
    my $sheet_id = $args->{sheetid};
    my $range = "A:Z"; # default range
    if (exists $args->{range}) {
        $range = $args->{range};
    }
    my ($return_code, $contents) = 
        append_spreadsheet($token, $sheet_id, $range, $args->{appendtype}, $args->{values}); # to create

    cleanup_credentials($credsfile);
    
    print "Return code: $return_code\n" if ($DEBUGMODE > 0);
    print "Contents: ", $contents, "\n" if ($DEBUGMODE > 0);

    if ($return_code =~ /403/) {
        return "You are forbidden from accessing this spreadsheet.\n";
    } elsif ($return_code =~ /404/) {
        return "Spreadsheet not found. Please check the Sheet ID.\n";
    } else {
        return $contents;
    } 
  }
);

# Tool: fetch_google_sheet_row
# Description: Used to fetch a row from a google sheet based on search criteria
$server->tool(
  name         => 'fetch_google_sheet_row',
  description  => 'Fetch a row from a google sheet based on search. Range is optional (default A:Z). You can specify it with columns and rows, e.g. A1:Z378',
  input_schema => {type => 'object', properties => {sheetid => {type => 'string'}, password => {type => 'string'}, credential => {type => 'string'}, range => {type => 'string'}, searchfield => {type => 'string'}, searchvalue => {type => 'string'}}, required => ['sheetid','searchfield','searchvalue']},
  code         => sub ($tool, $args) {
    my ($auth, $credsfile, $error) = get_authenticated_service_account($args, 'fetch_google_sheet_row');
    return $error if $error;

    my $token = $auth->get_token();
    print "token: $token\n" if ($DEBUGMODE > 0);
    my $sheet_id = $args->{sheetid};
    my $range = "A:Z"; # default range
    if (exists $args->{range}) {
        $range = $args->{range};
    }
    my ($return_code, $contents) = 
        read_spreadsheet_search($token, $sheet_id, $range, $args->{searchfield}, $args->{searchvalue});

    cleanup_credentials($credsfile);
    
    print "Return code: $return_code\n" if ($DEBUGMODE > 0);
    print "Contents: ", $contents, "\n" if ($DEBUGMODE > 0);

    if ($return_code =~ /403/) {
        return "You are forbidden from accessing this spreadsheet.\n";
    } elsif ($return_code =~ /404/) {
        return "Spreadsheet not found. Please check the Sheet ID.\n";
    } else {
        return $contents;
    } 
  }
);

# Tool: fetch_google_sheet
# Description: Used to fetch a full google sheet
$server->tool(
  name         => 'fetch_google_sheet',
  description  => 'Fetch a full google sheet',
  input_schema => {type => 'object', properties => {sheetid => {type => 'string'}, password => {type => 'string'}, credential => {type => 'string'},  range => {type => 'string'}}, required => ['sheetid']},
  code         => sub ($tool, $args) {
    my ($auth, $credsfile, $error) = get_authenticated_service_account($args, 'fetch_google_sheet');
    return $error if $error;

    my $token = $auth->get_token();
    print "token: $token\n" if ($DEBUGMODE > 0);
    my $sheet_id = $args->{sheetid};
    my $range = "A:Z"; # default range
    if (exists $args->{range}) {
        $range = $args->{range};
    }
    my ($return_code, $contents) = 
        read_spreadsheet_raw($token, $sheet_id, $range);

    cleanup_credentials($credsfile);
    print "Return code: $return_code\n" if ($DEBUGMODE > 0);
    print "Contents: ", $contents, "\n" if ($DEBUGMODE > 0);

    if ($return_code =~ /403/) {
        return "You are forbidden from accessing this spreadsheet.\n";
    } elsif ($return_code =~ /404/) {
        return "Spreadsheet not found. Please check the Sheet ID.\n";
    } else {
        return $contents;
    } 
  }
);

# Tool: batch_update_google_sheet
# Description: Used to batch update values in a google sheet
$server->tool(
  name         => 'batch_update_google_sheet',
  description  => 'Batch update values in a google sheet. Updates a specific range. range should be specified in A1 notation, e.g. Sheet1!A1:C3. values should be an array of arrays representing the rows and columns to update.  For instance, one row would use a range of say "Sheet1!A3:E3" and values of "["2026/01/09","Perl MCP2000","myperlsession02","xxx","No"]". You can also specify valueInputOption (RAW or USER_ENTERED), default is USER_ENTERED.',
  input_schema => {
    type => 'object',
    properties => {
      sheetid => {type => 'string'},
      password => {type => 'string'},
      credential => {type => 'string'},
      range => {type => 'string'},
      values => {type => 'array'},
      valueInputOption => {type => 'string'}
    },
    required => ['sheetid', 'range', 'values']
  },
  code         => sub ($tool, $args) {
    my ($auth, $credsfile, $error) = get_authenticated_service_account($args, 'batch_update_google_sheet');
    return $error if $error;

    my $token = $auth->get_token();
    print "token: $token\n" if ($DEBUGMODE > 0);
    my $sheet_id = $args->{sheetid};

    my $values = $args->{values};
    # Ensure values is an array of arrays
    if (scalar @$values > 0 && ref($values->[0]) ne 'ARRAY') {
         $values = [$values];
    }
    
    my $data = [{
        range => $args->{range},
        values => $values
    }];
    
    my $valueInputOption = 'USER_ENTERED';
    if (exists $args->{valueInputOption}) {
        $valueInputOption = $args->{valueInputOption};
    }

    my ($return_code, $contents) = 
        batch_update_spreadsheet($token, $sheet_id, $data, $valueInputOption);

    cleanup_credentials($credsfile);
    print "Return code: $return_code\n" if ($DEBUGMODE > 0);
    print "Contents: ", $contents, "\n" if ($DEBUGMODE > 0);

    if ($return_code =~ /403/) {
        return "You are forbidden from accessing this spreadsheet.\n";
    } elsif ($return_code =~ /404/) {
        return "Spreadsheet not found. Please check the Sheet ID.\n";
    } else {
        return $contents;
    } 
  }
);

# Helper Subroutines for Tools

sub get_authenticated_service_account($args, $tool_name) {
    my $credsfile = 'credentials.json';
    my $temp_creds_file;

    # If we mounted in as a directory with Docker, use that path
    if ((-d "/creds") && (-e "/creds/credentials.json")) {
            $credsfile = '/creds/credentials.json';
    }

    # If they passed in a credential, we will use that first
    if (exists $args->{credential}) {
        my $randomint = int(rand(10000));
        my $etime = time();

        $temp_creds_file = "/tmp/mcp_google_cred_$etime$randomint.json";
        open(my $fh, '>', $temp_creds_file) or die "Could not open file '$temp_creds_file' $!";
        print $fh $args->{credential};
        close $fh;
        $credsfile = $temp_creds_file;
        print "Using temporary credential file at $temp_creds_file\n" if ($DEBUGMODE > 0);
    } else {
        # Check on Password if required, does not apply if using custom credential
        if ($USAGEPASS ne 'nopassword'){
            if (exists $args->{password}) {
                if ($args->{password} ne $USAGEPASS) {
                    print "Invalid password provided. Rejecting request.\n";
                    return (undef, undef, "Invalid password provided for $tool_name tool.\n");
                } else {
                    print "Password accepted.\n";
                }
            } else {
                print "No password provided. Rejecting request.\n";
                return (undef, undef, "No password provided for $tool_name tool.\n");
            }
        } else {
            print "No usage password set, skipping password check.\n";
        }
    }
    
    my @scopes = ('https://www.googleapis.com/auth/spreadsheets',
                  'https://www.googleapis.com/auth/drive');
    
    my $auth =  WWW::Google::Cloud::Auth::ServiceAccount->new(
        credentials_path => $credsfile,
        scope => join " ", @scopes
    );

    return ($auth, $temp_creds_file, undef);
}

sub cleanup_credentials($credsfile) {
    if (defined $credsfile && -e $credsfile) {
        unlink $credsfile;
        print "Deleted temporary credential file at $credsfile\n" if ($DEBUGMODE > 0);
    }
}

# Google Worksheet Helper Functions
sub read_spreadsheet_search($token, $id, $range, $field, $match) {
    my $encoder = URI::Encode->new();
    my $finalHash = {};

    my $url = sprintf 'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s', 
        $id, $range;
    print "Request URL: $url\n" if ($DEBUGMODE > 0);
    my $result = send_google_drive_get_request($url, $token);
    my $status_line = $result->status_line;
    if (!$result->is_success) {
        return $status_line;
    } else {
        print $status_line, "\n" if ($DEBUGMODE > 0);
    }
    my $result_hash = decode_json( $result->content );
    $finalHash->{match_field_index} = -1;
    print "INITIAL RETURN: ", Dumper($result_hash), "\n" if ($DEBUGMODE > 0);
    if (exists $result_hash->{values}) {
        foreach my $row_index (0 .. $#{ $result_hash->{values} }) {
            my $row = $result_hash->{values}[$row_index];
            if ($row_index == 0) {
                # store the keys from the table header
                foreach my $col_index (0 .. $#{ $row }) {
                    $finalHash->{headers}[$col_index] = $row->[$col_index];
                    if ($row->[$col_index] =~ /$field/) {
                        # store the index of this column
                        # this is used for matching to match
                        $finalHash->{match_field_index} = $col_index; 
                    }
                    print "Header Cell value: ", $row->[$col_index], "\n" if ($DEBUGMODE > 0);
                }
            } else {
                # store the data values
                if ($finalHash->{match_field_index} >= 0) {
                    # only store rows that match the match criteria
                    if ($row->[$finalHash->{match_field_index}] !~ /$match/) {
                        print "Skipping row $row_index since it does not match $match on field index ", $finalHash->{match_field_index}, "\n" if ($DEBUGMODE > 0);
                        next;
                    } else {
                        print "Row $row_index matches $match on field index ", $finalHash->{match_field_index}, "\n" if ($DEBUGMODE > 0);

                        foreach my $col_index (0 .. $#{ $row }) {
                            my $cell_value = $row->[$col_index];
                            $finalHash->{values}[$col_index]->{$finalHash->{headers}[$col_index]} = $cell_value;
                            print "Storing in hash: values $col_index of key ", $finalHash->{headers}[$col_index], " => $cell_value\n" if ($DEBUGMODE > 0);
                        }
                    }
                }
            }
        }
    } else {
        print "No values found in the response.\n";
    }
  
    print "result_final_hash: ", Dumper($finalHash), "\n" if ($DEBUGMODE > 0);
    return $status_line, Dumper($finalHash);
}

# Google Worksheet Helper Functions
sub read_spreadsheet_raw($token, $id, $range) {
    my $encoder = URI::Encode->new();
    my $finalHash = {};

    my $url = sprintf 'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s', 
        $id, $range;
    print "Request URL: $url\n" if ($DEBUGMODE > 0);
    my $result = send_google_drive_get_request($url, $token);
    my $status_line = $result->status_line;
    if (!$result->is_success) {
        return $status_line;
    } else {
        print $status_line, "\n" if ($DEBUGMODE > 0);
    }
    my $result_hash = decode_json( $result->content );

    print "status_line: $status_line\n" if ($DEBUGMODE > 0);
    print "result_hash: ", Dumper($result_hash), "\n" if ($DEBUGMODE > 0);

    if (exists $result_hash->{values}) {
        return $status_line, Dumper($result_hash->{values});
    } 
    return $status_line, Dumper($result_hash);
}

# Appending a row to a google sheet
sub append_spreadsheet($token, $sheet_id, $range, $appendtype, $values) {
    # Default append type handling
    my $insertDataOption = "INSERT_ROWS";
    if (defined $appendtype && $appendtype eq 'overwrite') {
        $insertDataOption = "OVERWRITE";
    }

    # Ensure values is an array of arrays. 
    # If the user passed a simple array [1, 2, 3], wrap it [[1, 2, 3]].
    if (scalar @$values > 0 && ref($values->[0]) ne 'ARRAY') {
         $values = [$values];
    }

    # URL Encode range if necessary (simple spaces handling)
    $range =~ s/ /%20/g;

    my $url = sprintf 'https://sheets.googleapis.com/v4/spreadsheets/%s/values/%s:append?valueInputOption=USER_ENTERED&insertDataOption=%s', 
        $sheet_id, $range, $insertDataOption;
        
    print "Request URL: $url\n" if ($DEBUGMODE > 0);
    
    my $body = {
        values => $values
    };
    
    my $result = send_google_drive_post_request($url, $token, $body);
    my $status_line = $result->status_line;
    if (!$result->is_success) {
        print "Error content: " . $result->content . "\n" if ($DEBUGMODE > 0);
        return $status_line, $result->content;
    } else {
        print $status_line, "\n" if ($DEBUGMODE > 0);
    }
    
    my $result_hash = decode_json( $result->content );
    print "status_line: $status_line\n" if ($DEBUGMODE > 0);
    print "result_hash: ", Dumper($result_hash), "\n" if ($DEBUGMODE > 0);
    
    # For append, the response usually contains 'updates' with 'updatedRange', etc.
    if (exists $result_hash->{updates}) {
        return $status_line, Dumper($result_hash->{updates});
    } 
    return $status_line, Dumper($result_hash);
}

# Batch update values in a google sheet
sub batch_update_spreadsheet($token, $sheet_id, $data, $value_input_option) {
    
    my $url = sprintf 'https://sheets.googleapis.com/v4/spreadsheets/%s/values:batchUpdate', $sheet_id;
        
    print "Request URL: $url\n" if ($DEBUGMODE > 0);
    
    my $body = {
        valueInputOption => $value_input_option,
        data => $data
    };
    
    print "Request Body: ", Dumper($body), "\n" if ($DEBUGMODE > 0);
    
    my $result = send_google_drive_post_request($url, $token, $body);
    my $status_line = $result->status_line;
    if (!$result->is_success) {
        print "Error content: " . $result->content . "\n" if ($DEBUGMODE > 0);
        return $status_line, $result->content;
    } else {
        print $status_line, "\n" if ($DEBUGMODE > 0);
    }
    
    my $result_hash = decode_json( $result->content );
    print "status_line: $status_line\n" if ($DEBUGMODE > 0);
    print "result_hash: ", Dumper($result_hash), "\n" if ($DEBUGMODE > 0);
    
    return $status_line, Dumper($result_hash);
}

sub send_google_drive_post_request( $url, $token, $content_hash ) {
    my @headers = get_headers($token);
    push @headers, 'Content-Type' => 'application/json';
    
    my $json_content = encode_json($content_hash);
    
    my $req = HTTP::Request->new('POST', $url, \@headers, $json_content);
    my $ua = LWP::UserAgent->new();
    my $res = $ua->request($req);
    return $res;
}

sub send_google_drive_get_request( $url, $token ) {
    my @headers = get_headers($token);
    my $req = HTTP::Request->new('GET', $url, \@headers);
    my $ua = LWP::UserAgent->new();
    my $res = $ua->request($req);
    return $res;
}

sub get_headers($token) {
    return
        'User-Agent'      => 'Mozilla/8.0',
        'Accept-Encoding' => 'gzip, deflate',
        'Accept'          => '*/*',
        'Connection'      => 'keep-alive',
        "Authorization"   => "Bearer $token";
}

any '/mcp' => $server->to_action;
app->start;

	# Monkey-patch MCP::Server to fix protocol negotiation
{
    no warnings 'redefine';
    *MCP::Server::_handle_initialize = sub ($self, $params) {
        # Echo back the client's protocol version to ensure compatibility
        my $version = $params->{protocolVersion} // '2025-11-25';
        return {
            protocolVersion => $version,
            capabilities    => {prompts => {}, resources => {}, tools => {}},
            serverInfo      => {name => $self->name, version => $self->version}
        };
    };
}