Grist-plugin-api.js

Ask for Help
1

Hello there !

I am trying to retrieve data from a filtered table in a view inside a custom widget with grist-plugin-api.
I firstly tried grist.docApi.fetchTable("Animals").then((resp) => { do stuff};
Works nicely but I filter Table in the current view it keeps the initial values (sounds logic)

so I tried instead grist.viewApi.fetchSelectedTable("Animals").then((resp) => {do stuff};
But it does not work neither, it returns all the data while the view is filtered.

Is there a simple way to do this ?

2

Hi @Sylvain_Page,

Unfortunately no, the custom widget doesn’t have access to the filtered data, you will always receive all rows from a table.

3

@jarek , thanks.
Is there anychance to get filter information from the custom-widget so we can make the filter logic inside the custom widget ?
If not Last option → Move filtering in a filter table, adding it as an editable card and fetching the information from this table to do the logic and filtering the view accordingly;

4

If the filters are on the custom widget itself, and you are looking for the filtered data in the table backing the custom widget itself, then you can receive filtered data by listening with grist.onRecords, see for example:
https://public.getgrist.com/doc/911KcgKA95oQ~jvUkurRFSHTY7YwVVMChmB~26
But I don’t know if that is what you are looking for @Sylvain_Page?

By the way, fetchSelectedTable() doesn’t take an argument, it just returns (all) data from the table backing the custom widget.

5

Hi @paul-grist ,

Thanks my bad indeed I tried to filter the table instead of the widget.
Your example is a good start as filtering with onRecords works well.
Yet in my example the chart does not update when I am filtering and remains the same.
Basically I need to perform basic test on smaller data, I suspect plotlyjs to freeze with large superposed set of data.

Thanks for your support, I’ll give you result of investigation in a couple of days.

1 Like
6

Hi @paul-grist ,

Thanks for your help, I’m posting there my custom widget with plotlyjs mixed chart (bars + line) & column mapping. It could certainly be pushed further adding special option for bar type etc…
I’ve seen lots of peaple wanting better chart inetgration, but I do believe it is quite complex to handle all cases and customization… (having a look at grist-core source for the charting file glupsss).

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title>My Grist Chart Widget</title>
    <!-- Include Plotly.js library -->
    <script src="https://cdn.plot.ly/plotly-latest.min.js"></script>
    <script src="https://docs.getgrist.com/grist-plugin-api.js"></script>
  </head>

  <body>
    <!-- Create a container element for the chart -->
    <div id="myDiv" style="position: relative; height: 100vh"></div>

    <script>
      // Wait for the Grist API to be ready
      grist.ready({
        columns: ["Date", "Result", "Target"],
        requiredAccess: "read table",
      });

      const plotDiv = document.getElementById("myDiv");

      // Set the height and width of the Plotly chart to match the iframe
      const layout = {
        autosize: true,
        margin: {
          l: 5,
          r: 5,
          t: 5,
          pad: 0,
        },
      };

      const config = {
        displayModeBar: false, // this is the line that hides the bar.
        responsive: true,
      };

      // When a new record is selected in the Grist table, render a chart based on its data
      grist.onRecords(function (records, mappings) {
        let mapped = grist.mapColumnNames(records);
        if (mapped) {
          // mJson = mapped Json from mapped column
          let mResults = mapped.map(({ Result, Date }) => ({ Result, Date }));
          let mTargets = mapped.map(({ Target, Date }) => ({ Target, Date }));
          mTargets.sort((a, b) => {
            return a.Date - b.Date;
          });

          let traceResult = {
            x: mResults.map((obj) => obj.Date),
            y: mResults.map((obj) => obj.Result),
            type: "bar",
          };
          let traceTarget = {
            x: mTargets.map((obj) => obj.Date),
            y: mTargets.map((obj) => obj.Target),
            type: "scatter",
          };

          let data = [traceResult, traceTarget];
          Plotly.newPlot("myDiv", data, layout, config);
        } else {
          // Helper returned a null value. It means that not all
          // required columns were mapped.
          console.error("Please map all columns");
        }
      });
    </script>
  </body>
</html>
1 Like
7

If you apply filters directly to the custom widget (not another widget with the same data table) then grist.viewApi.fetchSelectedTable (or just grist.fetchSelectedTable) will use those filters. It also uses the filtering from ‘Select by’. Maybe this was only implemented recently, although I can’t see any sign of such changes.

8

Hello @Alex_Hall ,

Yes this is now ok for me.I’m using grist.onRecords and filter widget, works well.

9

What am I doing wrong???

// Request access to specific columns from all relevant tables
grist.ready({
  requiredAccess: 'read table',
  columns: {
    "Empresas": ["id", "Nome_da_Empresa"],
    "Sessoes": ["id", "RefSessao", "NomeSessao"],
    "PDCA": ["id", "EmpresaRef", "PraticaRef", "Nota_da_Pratica"],
    "Resultados": ["id", "RefEmpresa", "Num_TipoIndicRef", "PONTUACAO"]
  }
});

grist.onRecord(async (record) => {
  const empresaContainer = document.getElementById("empresa-container");
  const tabelasContainer = document.getElementById("tabelas-container");

  if (!record) {
    empresaContainer.innerHTML = "<p>Nenhuma empresa selecionada.</p>";
    tabelasContainer.innerHTML = "";
    return;
  }

  const empresaId = record.id;
  empresaContainer.innerHTML = `<h2>Empresa Selecionada: ${record.Nome_da_Empresa || "N/A"}</h2>`;

  let tabelasHtml = "<h2>Metadata Debug</h2>";

  try {
    // Fetch metadata for each table to confirm column availability
    const sessoesMeta = await grist.docApi.fetchTableMetadata("Sessoes");
    const pdcaMeta = await grist.docApi.fetchTableMetadata("PDCA");
    const resultadosMeta = await grist.docApi.fetchTableMetadata("Resultados");

    console.log("Sessoes metadata:", sessoesMeta);
    console.log("PDCA metadata:", pdcaMeta);
    console.log("Resultados metadata:", resultadosMeta);

    tabelasHtml += `<h3>Sessoes Metadata</h3><pre>${JSON.stringify(sessoesMeta, null, 2)}</pre>`;
    tabelasHtml += `<h3>PDCA Metadata</h3><pre>${JSON.stringify(pdcaMeta, null, 2)}</pre>`;
    tabelasHtml += `<h3>Resultados Metadata</h3><pre>${JSON.stringify(resultadosMeta, null, 2)}</pre>`;

    tabelasContainer.innerHTML = tabelasHtml;

    // Optional: Uncomment the following block to fetch and display actual records after confirming metadata
    /*
    const sessoes = await grist.docApi.fetchTable("Sessoes");
    const pdcaRecords = await grist.docApi.fetchTable("PDCA");
    const resultados = await grist.docApi.fetchTable("Resultados");

    console.log("Sessoes:", sessoes.records);
    console.log("PDCA Records:", pdcaRecords.records);
    console.log("Resultados:", resultados.records);

    let dataHtml = "<h2>Dados Filtrados</h2>";
    dataHtml += `<h3>Sessoes (${sessoes.records.length})</h3><pre>${JSON.stringify(sessoes.records.slice(0, 5), null, 2)}</pre>`;
    dataHtml += `<h3>PDCA Records (${pdcaRecords.records.length})</h3><pre>${JSON.stringify(pdcaRecords.records.slice(0, 5), null, 2)}</pre>`;
    dataHtml += `<h3>Resultados (${resultados.records.length})</h3><pre>${JSON.stringify(resultados.records.slice(0, 5), null, 2)}</pre>`;

    tabelasContainer.innerHTML += dataHtml;
    */

  } catch (error) {
    console.error("Error fetching metadata or related data:", error);
    tabelasContainer.innerHTML = "<p>Erro ao carregar dados das tabelas relacionadas.</p>";
  }
});

This results in “Erro ao carregar dados das tabelas relacionadas.” (Error loading data from related tables)

1 Like
10

hi Sylvain… my code above was for the Widget Builder widget.

It seems that unlike Custom Widgets, the Widget Builder absolutely CAN´T retrieve data from extra tables… I think because it’s like a sandbox, it can´t access the Grist Plugin API.

What some Custom Plugins, like the Invoice plugin do, and can be used by the widget creator, is to have a formula in a cell that retrieves ALL DATA (or filtered data) from all references to a row.

Then you access that data, since it’s in the row of the table you are already connected to.

like this

# Step 1: Fetch all necessary data
sessoes = list(Sessoes.all)
pdca_records = PDCA.lookupRecords(EmpresaRef=$id)
resultados = Resultados.lookupRecords(RefEmpresa=$id)

# Step 2: Create a summarized structure
summary = {
    "sessoes": [{"id": sessao.id, "nome": sessao.Sessao_NomeSessao} for sessao in sessoes],
    "pdca": [{"id": record.id, "nota": record.Nota_da_Pratica} for record in pdca_records],
    "resultados": [{"id": resultado.id, "pontuacao": resultado.PONTUACAO} for resultado in resultados],
}

# Step 3: Return as JSON string
import json
return json.dumps(summary)

which returns
{“sessoes”: [{“id”: 1, “nome”: “PENSAMENTO SIST\u00caMICO”}, {“id”: 2, “nome”: “COMPROMISSO COM AS PARTES INTERESSADAS”}, {“id”: 3, “nome”: “APRENDIZADO ORGANIZACIONAL E INOVA\u00c7\u00c3O”}, {“id”: 4, “nome”: “ADAPTABILIDADE”}, {“id”: 5, “nome”: “LIDERAN\u00c7A TRANSFORMADORA”}, {“id”: 6, “nome”: “DESENVOLVIMENTO SUSTENT\u00c1VEL”}, {“id”: 7, “nome”: “ORIENTA\u00c7\u00c3O POR PROCESSOS”}], “pdca”: [{“id”: 1, “nota”: 81.25}, {“id”: 2, “nota”: 75.0}, {“id”: 3, “nota”: 68.75}, {“id”: 4, “nota”: 0.0}, {“id”: 5, “nota”: 56.25}, {“id”: 6, “nota”: 0.0}, {“id”: 7, “nota”: 0.0}, {“id”: 8, “nota”: 50.0}, {“id”: 9, “nota”: 0.0}, {“id”: 10, “nota”: 56.25}, {“id”: 11, “nota”: 0.0}, {“id”: 12, “nota”: 50.0}, {“id”: 13, “nota”: 62.5}, {“id”: 14, “nota”: 0.0}, {“id”: 15, “nota”: 0.0}, {“id”: 16, “nota”: 0.0}, {“id”: 17, “nota”: 0.0}, {“id”: 18, “nota”: 0.0}, {“id”: 19, “nota”: 0.0}, {“id”: 20, “nota”: 0.0}, {“id”: 21, “nota”: 0.0}, {“id”: 22, “nota”: 0.0}, {“id”: 23, “nota”: 0.0}, {“id”: 24, “nota”: 0.0}, {“id”: 25, “nota”: 0.0}, {“id”: 26, “nota”: 0.0}, {“id”: 27, “nota”: 0.0}, {“id”: 28, “nota”: 0.0}, {“id”: 29, “nota”: 0.0}, {“id”: 30, “nota”: 0.0}, {“id”: 31, “nota”: 0.0}, {“id”: 32, “nota”: 0.0}, {“id”: 33, “nota”: 0.0}, {“id”: 34, “nota”: 0.0}, {“id”: 35, “nota”: 18.75}, {“id”: 36, “nota”: 0.0}, {“id”: 37, “nota”: 0.0}, {“id”: 38, “nota”: 18.75}, {“id”: 39, “nota”: 0.0}, {“id”: 40, “nota”: 0.0}], “resultados”: [{“id”: 1, “pontuacao”: 80}, {“id”: 2, “pontuacao”: 80}, {“id”: 3, “pontuacao”: 60}, {“id”: 4, “pontuacao”: 40}, {“id”: 5, “pontuacao”: 0}, {“id”: 6, “pontuacao”: 0}, {“id”: 7, “pontuacao”: 0}, {“id”: 8, “pontuacao”: 0}, {“id”: 9, “pontuacao”: 0}, {“id”: 10, “pontuacao”: 0}, {“id”: 11, “pontuacao”: 0}, {“id”: 12, “pontuacao”: 0}, {“id”: 13, “pontuacao”: 0}, {“id”: 14, “pontuacao”: 0}, {“id”: 15, “pontuacao”: 0}, {“id”: 16, “pontuacao”: 0}, {“id”: 17, “pontuacao”: 0}, {“id”: 18, “pontuacao”: 0}, {“id”: 19, “pontuacao”: 0}, {“id”: 20, “pontuacao”: 0}, {“id”: 21, “pontuacao”: 0}, {“id”: 22, “pontuacao”: 0}]}

11

Turns out it CAN get data from extra tables.
you can still read data from other tables—but not reactively. In other words, you must query those tables on demand (using a helper method from the document API, such as the one that accepts a table ID and returns its records) and you need full document access to do so. (as it needs to REQUERY, changes through this method are NOT immediate. It makes a requery every 5 seconds.

HOWEVER, I suppose if you have two linked tables, you can have a column that changes whenever data in the other table changes. As connected tables react immediately, the change in the connected table (due to change in a linked/referenced table) can trigger the re query

here is the code I used in the Custom Widget Builder

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Grist Document Explorer</title>
    <script src="https://docs.getgrist.com/grist-plugin-api.js"></script>
    <style>
      body { font-family: sans-serif; padding: 10px; }
      .table { border: 1px solid #ccc; margin-bottom: 20px; padding: 10px; }
      .table h2 { margin-top: 0; }
      table { border-collapse: collapse; width: 100%; }
      th, td { border: 1px solid #999; padding: 4px 8px; text-align: left; }
      th { background-color: #f0f0f0; }
    </style>
  </head>
  <body>
    <div id="content">Loading data...</div>
    <script>
      (async function(){
        // Request full access so we can list tables and query any data
        grist.ready({ requiredAccess: 'full' });
      
        // Function to fetch and display table information
        async function fetchAndDisplayData(){
          const contentDiv = document.getElementById('content');
          let html = '<h1>Tables in Document</h1>';
          try {
            // List all tables in the document
            const tables = await grist.docApi.listTables();
            // For demonstration, only work with the first two tables
            for(let i = 0; i < Math.min(2, tables.length); i++){
              const tableId = tables[i];
              html += `<div class="table"><h2>Table: ${tableId}</h2>`;
              
              // Fetch the complete table data.
              // Note: fetchTable returns an object mapping column names to arrays of cell values.
              const tableData = await grist.docApi.fetchTable(tableId);
              const columns = Object.keys(tableData);
              
              // Display column names
              html += '<h3>Columns:</h3><ul>';
              columns.forEach(col => {
                html += `<li>${col}</li>`;
              });
              html += '</ul>';
              
              // Display table rows in a HTML table.
              if (columns.length > 0) {
                // Assume all columns have the same number of rows
                const rowCount = tableData[columns[0]].length;
                html += `<h3>Data (${rowCount} rows):</h3>`;
                html += '<table><thead><tr>';
                columns.forEach(col => {
                  html += `<th>${col}</th>`;
                });
                html += '</tr></thead><tbody>';
                for(let row = 0; row < rowCount; row++){
                  html += '<tr>';
                  columns.forEach(col => {
                    html += `<td>${tableData[col][row]}</td>`;
                  });
                  html += '</tr>';
                }
                html += '</tbody></table>';
              }
              html += '</div>';
            }
          } catch(e) {
            html = 'Error fetching data: ' + e;
          }
          contentDiv.innerHTML = html;
        }
      
        // Initial fetch
        await fetchAndDisplayData();
      
        // Re-query the data every 5 seconds to update on any changes.
        setInterval(fetchAndDisplayData, 5000);
      
        // Alternatively, if you wish to react to Grist data-change events (for the currently selected table),
        // you could subscribe to grist.onRecords. For a multi–table view, polling is a straightforward solution.
      })();
    </script>
  </body>
</html>

RESULT

image
image1621×780 39.2 KB