How do I access a specific record in a Grist Table via API?

Developers
1

I am trying to pull the contents of a specific row in a table via API.
I have a table named btcorders and the first sheet is called BTCPay

The url for this table looks like the following
https://docs.getgrist.com/{alphanumeric}/btcorders
.
With no other place to look, I guessed that docId is the alphanumeric and tableId is btcorders. Is this correct?

Then, I sent the GET URL in this format
$url = 'https://docs.getgrist.com/api/docs/{alphanumeric}/tables/btcorders/records?’.$pquery;

$pquery is the url-encoded query

Converting the JSON back into an array, this is what was returned

array(1) { [“error”]=> string(27) “Table not found “btcorders”” }

If I add the name of the sheet containing the data to the url like so /BTCPay/records, then this error pops up

array(1) { [“error”]=> string(172) “not found: /dw/doc-worker-172-31-30-184/v/fcb3a642e/api/docs/{alphanumeric}/tables/btcorders/BTCPay/records?filter%3D%7B%22invoice_id%22%3A+%22BohCYePUQZm8EiB3mSvBb4%22%5D%7D” }

It appears as if I’m not giving the query the correct information in the beginning to find the file. So how should this url be structured?

<?php

include ('loadenv.php');

// BEGIN gristQueryt() Check Grist table btcorders for payment verification
   function gristQuery($pquery) {

        $url = 'https://docs.getgrist.com/api/docs/{alphanumeric}/tables/btcorders/BTCPay/records?'.$pquery;
        
        $curl = curl_init($url);
        curl_setopt($curl, CURLOPT_URL, $url);
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);

        $headers = array(
            'Authorization: Bearer '.$_ENV['GRIST_API_KEY']
        );

        curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
        //for debug only!
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

        $resp = curl_exec($curl);
        curl_close($curl);
    //	var_dump($resp);
        $output = json_decode($resp, true);
    //	echo "<br>var_dump of output is <br>";
    //	var_dump($output);
        
        
        return $output;

   }
// END gristQueryt() Check Grist table btcorders for payment verification

   $invoice_id = 'BohCYePUQZm8EiB3mSvBb4';

   $query_string = 'filter={"invoice_id": "'.$invoice_id.'"]}';
   $query = urlencode($query_string);

   $record = gristQuery($query);

   var_dump($record);

?>
2

Hi Michael,

Here is a sample php code that works for me. When you use your API key, you should be able
to invoke it as it is. It gets records from one of Grist’s templates (link) and filters them by the order id column.
You can read more about the API in the help center API reference - Grist Help Center. Each endpoint has an accordion section with examples how to construct URLs to the Grist API, for example:
image

<?php
   function gristQuery($pquery) {
    // This is a Grist template document located at https://templates.getgrist.com/eVgQezBkmQVc/Digital-Sales-CRM/p/2
    $docId = "eVgQezBkmQVccB9qV6ParT";
    $tableId = "Orders";
    // Here use your API key.
    $apiKey = "<<paste api key>>";
    $url = "https://docs.getgrist.com/api/docs/${docId}/tables/${tableId}/records?".$pquery;
    $curl = curl_init($url);
    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
    $headers = array(
      'Authorization: Bearer '.$apiKey
    );
    curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
    $resp = curl_exec($curl);
    curl_close($curl);
    $output = json_decode($resp, true);
    return $output;
   }

   $invoice_id = '123';
   $query_string = 'filter={"Order_No_": "'.$invoice_id.'"]}';
   $query = urlencode($query_string);
   $record = gristQuery($query);
   var_dump($record);
?>
3

Fantastic! It looks like that did the trick!
I had set the docId but used the name of the document btcorders instead of inserting the sheet/table Id which is BTCPay.

At first I was confused as the result spit out every record in the table until I read your answer more closely. Since my invoice_id is unique for each order, setting limit=1 gave me that single record only.

Thank you so much!