Plugin API - Fetch a single row from a table

Developers
1

Hi,

I kind of already asked this here RECORD() effciency - #3 by Mark_Daniel but hiding it in a reply to an existing topic probably wasn’t the best.

Using the plugin API in full document access mode is there an easy way to just fetch one row, ideally by table id and row id? I have found fetchSelectedRecord but that only operates on the selected table and fetchTable but the array of values format isn’t very convenient if you want to find a particular row, it looks like you would have to linear search the id array to get the array index and then use that to get the values from the other arrays. I guess if it’s guaranteed that the id array is in order I could binary search which would be at least be quicker. Is there a nice javascript way of slicing all the value arrays into a new object given the index?

Thanks,
Mark

2

I’d say it’s a legitimate feature request, because there indeed isn’t a way at the moment. We are working on improvements to this API, so something better may become available before long.

Meanwhile, what I can suggest is this bit of code to convert the result of fetchTable() (which is in a column-oriented format) to something more convenient:

function toRecordMap(columnData) {
  const fieldNames = Object.keys(columnData);
  return new Map(columnData.id.map((id, index) => {
    const values = fieldNames.map(col => [col, columnData[col][index]]);
    return [id, Object.fromEntries(values)];
  }));
}

This returns a JS Map. E.g. if

const m = toRecordMap(await grist.docApi.fetchTable(tableId));

then m.get(rowId) will return an object representing that one row.

3

Thanks, with a combination of that and a few library methods I’ve got something working.

Mark

4

Hello there,

I’m looking for a solution on a similar situtation, anf I’ve tried fetchSelectedRecord, fetchSelectedTab le and fetchTable with options but can’t seem to get the desired result.

Was there any updates on that? Is something better available since then?

Thank you in advance and sorry for the grave digging.
George

5

It’s a bit silly that neither a single-record option nor a filter option has gotten added to the API. One thing I can suggest is to sidestep the custom widget API entirely by using getAccessToken (get it once, ideally, and reuse for multiple API calls):

Something like this:

const tokenInfo = await grist.docApi.getAccessToken({readOnly: true}); 
async function fetchOneRecord(tableId, rowId) {
  const result = await fetch(`${tokenInfo.baseUrl}/tables/${tableId}/records?filter={"id":[${rowId}]}&auth=${tokenInfo.token}`);
  return await result.json();
}
1 Like
6

Hi Dmitry,

First of al thank you for your reply and time!

I’ve tried your suggestion and I’ve found that it works perfectly for the document owner, it fails for an editor who has full read/write access to the table.

  • The widget gets an access token and calls the REST API with a filter: /api/docs/.../tables/Events/records?filter={"id":[28]}&auth=...

  • For an editor, this API call fails with what appears to be a permission error, even though they can see and edit the record in the Grist UI.

My question is: Is this expected behavior? Are API tokens generated for non-owner roles intentionally restricted from making filtered GET requests? If so, is there a server configuration or an access rule we can set on our self-hosted instance to grant this specific API permission to editors?

Thank you!

George

7

It should work just fine for Editor. The widget itself needs Access Level of “Full document access” (because even a read-only token gives access outside of the selected table). Even viewers can make filtered GET requests.

BTW, I think I gave incorrect information earlier: fetchSelectedRecord accepts a rowId parameter, and it does seem possible to call fetchSelectedRecord(rowId). It’s only for the table the widget itself is tied to, however, so may or may not help you.