Extract data from google sheet cell without opening it

Questions & Suggestions
1

Hi, I often use google sheet to set variable in km.. but the problem is that every time I want to do this, I have to open google sheet then wait for it finish loading then copy and paste the data in the specified cell into a variable .. Isn't there a way I can access data in certain cells of a certain google sheet ( via the link ) without opening the sheet itself, maybe by using the terminal or something ? thank you

2

There might be a way. I'm very new to KM, so I don't have a full solution, but maybe part of one. I have a Wordpress site that is able to pull data from a Google sheet (must be publicly accessible) by running a PHP "shortcode" (Wordpress's way of allowing PHP). The PHP code pulls up the Google sheet link, but exports it (and reads it) as a .csv file every time the page is loaded. (The site visitor sees none of this, because the .csv file is downloaded and parsed on the server side.) Then it manipulates variables and generates HTML based on what's in the sheet at any given time. So my idea is that if you can run PHP code in KM (I think I saw a plugin that does that), then you might be able make a similar connection. Technically you'd still be downloading the sheet every time, but maybe in a way that's faster and possibly wouldn't rely on a browser.

Anyway, here's the bit of my PHP that exports & parses the Google Sheet into an array:

$header_rows = 5; //Num of rows, inc. labels, before actual data in spreadsheet
$spreadsheet_key = "{{**removed**}}";

//Oddly, the sheet key is needed twice in order for Google to export CVS:    
$spreadsheet_url="https://docs.google.com/spreadsheets/d/" . $spreadsheet_key .
"/export?format=csv&id=" . $spreadsheet_key;

//Get the Google sheet data
$file = fopen($spreadsheet_url,"r");
    $arr_length = 0;
    $row_count = 1;
    $sheet_content = array();
    while (($line = fgetcsv($file)) !== FALSE) 
    //$line is an array of the csv elements
    {
    	if ($row_count > $header_rows) { //skip header rows
			array_push($sheet_content,$line);
       		$arr_length++;
        }
        $row_count++;
    }
fclose($file);
3

Once you publish a spreadsheet, you can use direct links to specific cells (or ranges) and get their content.

The format of the link is the following:
https://docs.google.com/spreadsheets/d/e/[Google Sheet ID]/pub?output=tsv&range=A2:B5

You can also specify one cell only (e.g., "&range=A2")

If you try to open one of those direct links, the browser will download a .tsv file, including the content of the cell (or range) in plain text format.

That means that you can simply paste that link in the Keyboard Maestro action "GET URL," so that every time you run your macro, the value of the specific cell(s) will be automatically saved into a variable, without opening any browser or downloading any file.

2 Likes