#Refresh Table When Google Sheet Updated

1 messages · Page 1 of 1 (latest)

vague shadow
#

Hi,
I have a Google Sheet that can be updated by other users. What I would like to do is create a Refresh button that when the Google Sheet is updated then the table is updated.
Also is there a way to say that the Google Sheet Has been updated so that the users can hit the refresh button rather than do this every time?
Thanks

kind oxideBOT
#

Hello! I'm AppBot, your Appsmith support assistant. To create a Refresh button that updates a table when your Google Sheet is updated, you can follow these steps:

  1. Create a Query to Fetch Data: First, ensure you have a query set up to fetch data from your Google Sheet. This query will be used to refresh the data in your table.

  2. Add a Button Widget: Drag and drop a Button widget onto your canvas. This will serve as your Refresh button.

  3. Set Button onClick Event: Configure the onClick event of the button to run the query that fetches data from your Google Sheet. This will refresh the data in your table whenever the button is clicked.

{?{ fetchGoogleSheetData.run() }?}

  1. Detecting Updates: Currently, Appsmith does not support automatic detection of updates in Google Sheets. Users will need to manually click the Refresh button to update the table data?5:0?source?.

Unfortunately, there isn't a built-in feature to notify users when the Google Sheet has been updated. Users will need to click the Refresh button to ensure they have the latest data. If you have any more questions or need further assistance, feel free to ask!

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

My Query to fetch data is called SALIX_API
I have configured the button to run the query, what I would like to is that 2 select Widgets Are updated as well as my table.
My 1st is called Group_Select
My 2nd is called Cluster_Select

kind oxideBOT
#

To update both your table and the two Select widgets (Group_Select and Cluster_Select) when the Refresh button is clicked, you can follow these steps:

  1. Update the Table: You already have the button configured to run the SALIX_API query to refresh the table data.

  2. Update the Select Widgets: You need to ensure that the Select widgets are populated with the latest data from the SALIX_API query. You can do this by setting the Options property of each Select widget to use the data from the query.

For example, if your SALIX_API query returns a list of groups and clusters, you can set the Options property of the Group_Select widget like this:

{?{ SALIX_API.data.groups }?}

And for the Cluster_Select widget:

{?{ SALIX_API.data.clusters }?}

  1. Button onClick Configuration: Ensure that the button's onClick event is configured to not only run the SALIX_API query but also refresh the Select widgets. This can be done by setting the Options property as shown above, which will automatically update when the query is run.

Here's a complete example of how you might configure the button's onClick event:

{?{
SALIX_API.run(() => {
// Optionally, you can add additional logic here if needed
})
}?}

By setting the Options property of the Select widgets to use the data from the SALIX_API query, they will automatically update whenever the query is run?5:0?source?.

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

Where is the options property to add this to the Select Widget? Is this the onOptionChange Event.
Or is this in Source data? If it is in source data I already a Jsbinded to this to retrieve unique name
export default {
getUniqueGroup:()=>{
if (!Array.isArray(SALIX_API.data)){
return []}

 const UniqueGroup = Array.from(new Set(SALIX_API.data.map(Group=>Group.Group)));
 return UniqueGroup.map(Group=>({label:Group,value:Group}));    

}

}

kind oxideBOT
#

The Options property for a Select widget is where you define the list of options that the widget will display. It is not an event like onOptionChange. Since you already have a JS function to retrieve unique names, you can use this function to set the Options property.

Here's how you can set it up:

  1. Set the Options Property: In the Select widget's properties panel, find the Options property. You can bind your JS function to this property using the mustache syntax.

For Group_Select, you can set the Options property like this:

{?{ JSObject1.getUniqueGroup() }?}

This will ensure that the Select widget displays the unique groups retrieved by your JS function.

  1. Ensure Data Refresh: When the SALIX_API query is run, it will update the data, and since the Options property is bound to the JS function, the Select widget will automatically refresh with the new data.

By setting the Options property in this way, your Select widget will dynamically update whenever the underlying data changes, such as when the Refresh button is clicked and the SALIX_API query is run.

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

There is no Options property, within Data Source the JS for the Group Select Widget is
{{Unique_Group.getUniqueGroup.data}}
Clicking the refresh button the binding is {{SALIX_API.run()}}
This still does not refresh the select widgets

kind oxideBOT
#

To update the Select widgets (Group_Select and Cluster_Select) with the latest data from your SALIX_API query, you should use the Source Data property of the Select widget. This property allows you to specify an array of objects to display options in the widget?5:0?Appsmith Documentation.md?.

Since you have a JS function getUniqueGroup that returns the unique groups, you can bind this function to the Source Data property of the Group_Select widget. Here's how you can do it:

  1. Set the Source Data Property: In the Select widget's properties panel, find the Source Data property. You can bind your JS function to this property using the mustache syntax.

For Group_Select, set the Source Data property like this:

{?{ Unique_Group.getUniqueGroup.data }?}

  1. Ensure Data Refresh: When the SALIX_API query is run, it will update the data, and since the Source Data property is bound to the JS function, the Select widget will automatically refresh with the new data.

  2. Repeat for Cluster_Select: Similarly, if you have a function to get unique clusters, bind it to the Source Data property of the Cluster_Select widget.

By setting the Source Data property in this way, your Select widgets will dynamically update whenever the underlying data changes, such as when the Refresh button is clicked and the SALIX_API query is run?5:0?Appsmith Documentation.md?.

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

{{Unique_Group.getUniqueGroup.data}} is already in the data source for the Group_Select Widget and the refresh is not refreshing

Do I need to add extra lines to this JS

export default {
getUniqueGroup:()=>{
if (!Array.isArray(SALIX_API.data)){
return []}

 const UniqueGroup = Array.from(new Set(SALIX_API.data.map(Group=>Group.Group)));
 return UniqueGroup.map(Group=>({label:Group,value:Group}));    

}

}

kind oxideBOT
#

To ensure that the Select widget updates correctly when the SALIX_API query is run, you need to make sure that the JS function is correctly bound and that the query is executed before the function is called. Here are a few steps to troubleshoot and ensure it works:

  1. Ensure Query Execution: Make sure that the SALIX_API query is successfully executed when the Refresh button is clicked. You can add a success callback to the query execution to confirm this.

  2. Check JS Function Execution: Ensure that the JS function getUniqueGroup is being called after the query execution. You can add a console log inside the function to verify this.

  3. Re-run the JS Function: If the function is not automatically re-evaluating, you can force it to re-run by calling it explicitly after the query execution. You can do this by chaining the function call in the button's onClick event:

{?{
SALIX_API.run(() => {
Unique_Group.getUniqueGroup();
})
}?}

  1. Check for Errors: Ensure there are no errors in the console that might be preventing the function from executing correctly.

By following these steps, you should be able to ensure that the Select widget updates with the latest data from the SALIX_API query. If the issue persists, double-check the data returned by the query and ensure it matches the expected structure used in your JS function.

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

Thank-you that has worked, I needed to add the JS in the refresh button On Click Event

kind oxideBOT
#

I'm glad to hear that adding the JS function call in the Refresh button's onClick event worked for you! If you have any more questions or need further assistance with Appsmith, feel free to ask. I'm here to help!

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

I would Like to Select the Group_Select Widget and then the Cluster_Select shows the relevant clusters.
At the moment I have the following JS code in Unique_Cluster_Name, but is erroring saying duplicate name

export default {
getUniqueCluster:()=>{

const selectedGroup = Grp_Select.selectedOptionValue;

return SALIX_API.data.filter(ClusName=>ClusName["Group"]===selectedGroup).map(ClusName=>({ClusName:ClusName["Cluster Name"]}));

    if (!Array.isArray(SALIX_API.data)){

return []}

 const uniqueCluster = Array.from(new Set(SALIX_API.data.map(Cluster=>Cluster["Cluster Name"])));
 return uniqueCluster.map(ClusName=>({label:ClusName,value:ClusName}));    

}

}

kind oxideBOT
#

The error you're encountering is due to having unreachable code after the return statement in your getUniqueCluster function. You need to structure your function to handle the filtering and mapping correctly. Here's a revised version of your function:

export default {
getUniqueCluster: () => {
if (!Array.isArray(SALIX_API.data)) {
return [];
}

const selectedGroup = Grp_Select.selectedOptionValue;
const filteredClusters = SALIX_API.data
.filter(item => item["Group"] === selectedGroup)
.map(item => item["Cluster Name"]);

const uniqueClusters = Array.from(new Set(filteredClusters));
return uniqueClusters.map(clusterName => ({ label: clusterName, value: clusterName }));
}
}

Explanation:

  1. Check for Array: First, ensure that SALIX_API.data is an array. If not, return an empty array.
  2. Filter by Selected Group: Filter the data to only include items where the "Group" matches the selected group from Grp_Select.
  3. Extract and Deduplicate Cluster Names: Extract the "Cluster Name" from the filtered data and remove duplicates using Set.
  4. Map to Options Format: Map the unique cluster names to the format expected by the Select widget ({ label, value }).

Usage:

  • Bind this function to the Source Data property of the Cluster_Select widget using the mustache syntax:

{?{ Unique_Cluster_Name.getUniqueCluster() }?}

This setup will ensure that the Cluster_Select widget updates to show only the clusters relevant to the selected group in Group_Select.

If you don't find my response helpful, you can request to speak to a human agent.

vague shadow
#

That has worked, thank-you again

kind oxideBOT
#

You're welcome! I'm glad to hear that it worked for you. If you have any more questions or need further assistance with Appsmith, feel free to reach out. I'm here to help!

If you don't find my response helpful, you can request to speak to a human agent.