6
September
2021
Tutorial

Building an Expense Manager for your Startup!

0
 minutes

From startups to large multinational corporations, every organization needs a tool or an application to keep a track of expenses. These apps usually help different departments, for example, the CEO’s office or finance teams to keep track of salaries, reimbursements and other company expenses.

There are several tools that one can find to manage expenses, however, these are either expensive or lack some crucial features that do not serve a large number of use-cases and finance goals.

We think that building expense managers shouldn’t be too complicated. So, we built an expense management dashboard using Appsmith and Google Sheets, which can be used by an organisation to manage reimbursements expenses based on their monthly budget limitations. Additionally, admins of the app can also either approve or reject it based on the reimbursement request that is raised by an employee.

Here’s a sneak peek of what the app looks like.

CleanShot 2021-09-03 at 13.20.45@2x.png
Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.

Following are the table of contents:

  • Getting Started with Appsmith and Gsheets
  • Fetch Data from Google Sheet to Appsmith
  • Posting Data for Requesting Reimbursement
  • Expense Manager Admin
  • Adding some additional metrics to track expenses!

Set Up Google Sheets and Appsmith

Follow the steps below to use Google Sheets with Appsmith:

  1. Create a new account on Appsmith (it’s free!), if you are already an existing user, log in to your Appsmith account.
  2. Create a new application by clicking on the Create New button under the Appsmith dashboard.
  3. We’ll now see a new Appsmith app with an empty canvas and a sidebar with Widgets, APIs and DB Queries.
  4. Click on the + icon next to the APIs section and choose the Google Sheets option.
  5. Next, click on the New Datasource button and set the scope to Read and Write and click Authorise.
  6. This will ask us to log in from our Google Account, choose the account we want to access Google Sheets with and log in. After successful authorisation, this will redirect back to your Appsmith account.
  7. Now, you’ll find your Google Sheets Datasource under your APIs, and you can create the necessary queries by choosing this data source.

Awesome! Now that our Google Sheets Plugin is set up, let’s create a new Google Sheet and add the necessary fields required for managing expenses.

Following are the fields we’ll be considering:

Date
Requested On
Type
Reason
Amount
Status
Requested By
Attachment(s)
Comments from Admin

To make this more precise, we’ve made a sample Google Sheet with some mock data here. We’ll be using the same Sheet throughout this guide, and you can either follow with this or create your own based on our requirements.

docs.google.com/spreadsheets/d/1b7BuwDx0He4..

Fetch Data from Google Sheet to Appsmith

Now that we are connected to our Google Sheets data source, let’s connect to our Google Sheet and query all the data onto a list widget in Appsmith. To do this, navigate to the created data source under the APIs section and click on the New API button on the top right. Next, follow the below steps:

  • After clicking the New API button, you’ll be redirected to a new query tab, name your API to getReimbursement by double-clicking on the existing one.
  • Now set the method to Fetch Sheets Row, this method will query the data that’s present in the given Google Sheet.
  • In the SpreadSheet URL property, paste the URL of your Google Sheet, in this tutorial we’ll be following with the following URL:

https://docs.google.com/spreadsheets/d/1b7BuwDx0He41wtKYazxX3uJyIgQDHDRWyfyycL6mLMk/edit#gid=0

  • Next, in the Sheet name, add the name of our first sheet which is Reimbursement
  • The Table Heading Row Index property takes in the row number that basically has our column heading, we’ll set this to 1.
  • Next, we’ll set the Row Offset to 1 and Row limit to 100, this will query only the first 100 rows of our Google Sheet.
  • Lastly, click on the Run button on the top right, we should see the sheet information in JSON format in the response pane at the bottom.

Awesome, now that we have our data from the Google Sheet, let’s put this in a list; drag and drop a new table widget and paste the following in the Table Data property:

{{
    getReimbursement.data.filter((item) => {
        return item["Requested By"] == appsmith.user.name;
    })
}}
Quick Hack: For making the application more readable, let’s change the colour of the text based on the Reimbursement status. Appsmith allows us to write JS for styling widgets, now open the Text3 property pane and toggle JS in the text colour property and paste the following code:
Text1: {{currentItem.Reason}}
This will display the reason from the sheets row onto the list item

Text2: {{`Amount: ${currentItem.Amount} $`}}
Display’s the amount of the expense

Text3: {{`Status: ${currentItem.Status} $`}}
Display’s the status of the expense from the sheets row onto the list item

Text4: {{currentItem["Date"]}}
Display’s the status of the expense from the sheets row onto the list item

Text5: {{`Type: ${currentItem.Type}`}}
Display’s the Expense type from sheet

Text6: {{currentItem["Date"]}}
Display’s the date from the sheet

With this, our list widget is colourful and feature-rich.

Here’s how it looks like:

Post Data for Requesting Reimbursement

In this section, we’ll create a new form that will allow users to post new reimbursement requests to the admins. For this, let’s use the Form and Input components and Google Sheets integration on Appsmith. Follow the below steps:

  1. First, let’s add a container component to wrap all the widgets on the canvas.
  2. Drag and drop the Form widget onto the container widget, here we’ll see two button’s that’ll allow to submit a form and reset a form.
  3. Add the following widgets onto the form widget that’ll allow us to collect all the data to request a reimbursement.
  4. Select Widget (Name it as claimType)

We’ll add the select widget onto the form to set the type of reimbursement. Open the property-pane of the select widget and let’s add the options by pasting the following code:

{{
function () {
      let skills = ["Travel", "Hotel and Accomodation", "Food", "Medical", "Telephone","Fuel", "Imprest", "Other"];

      let options_list = skills.map((item) => {
        return {
          label: item,
          value: item,
        };
      });

      return options_list;
    }()
}}

Here we define an array of all the options and iterate through a for-loop and render them all in the select widget.

  • DatePicker Widget (Name it as expenseDate) The date picker widget allows us to add the date of the expense. To set the default date, open the property pane and set the Default Date to moment.now()
  • Input Widget (Name it as expenseDetail) Allows users to add the details of reimbursement.
  • Input Widget (Name it as expenseAmount) Allows the user to add the amount that’s requested for the reimbursement. Here, to make it more precise, ser the input type to float, to accept decimals in the given input.

Now that we have our form ready, let's write the logic to push the values from the form to the Google Sheet whenever submitted. Follow the below steps:

  • Create a new API from the existing Google Sheets data source and set the query method to Insert Sheet Row. This query method helps us insert new data to the Google Sheet from Appsmith.
  • Name the query as postReimbursement
  • Next, in the SpreadSheet URL property, add the link to our Google Sheet.
  • The Sheet Name will be Reimbursement as named in our Google Sheet.
  • The Table Heading Row Index will be the row where the names of the columns are listed; in our case, this will be 1,
  • Lastly, the Row Object takes a JSON object with keys set to the column names associated with the desired values. In our case, below is how the Row Object should look like:
{
    "Date": "{{expenseDate.formattedDate}}",
    "Requested On":"{{moment().format("L")}}",
    "Type": "{{claimType.selectedOptionValue}}",
    "Reason": "{{expenseDetail.text}}",
    "Amount": "{{expenseAmount.text}}",
    "Status": "Pending",
    "Requested By": "{{appsmith.user.name}}"
}

Here, the keys are the column names in the Google Sheet, and the values associated with them are the names of the input widgets. The .text method is used to pick the text that's written in the input widgets.

Lastly, in the form below for the submit button, set the on click property to Call an API and call the postReimbursement query from the options. With this, we should be able to add new rows to Google Sheet from Appsmith.

In the next section, we will build an Admin Dashboard where admins can approve or reject the requested reimbursement.

Expense Manager Admin

The expense manager admin can be only viewed by the admins of the application. Here we’ll be displaying all the information regarding the reimbursement’s and add an option to approve and reject them with a comment.

To build this, follow the steps below:

  1. Create a new page by clicking on the + icon next to the pages and rename it to Admin View.
  2. Add a container widget across the canvas to group all the widget’s Copy the getReimbursement query from Page1 to Admin View page
  3. We’ll now add a table to show all the pending reimbursements and add an option to approve and reject them. Drag and drop a table widget onto the container, open the property pane and set the Table Data to the following:
{{getReimbursement.data.filter((item) => {
  return item["Status"] == "Pending";
})}}

With this, we should see all the reimbursement requests with pending status on the table widget. Now, add to add options to approve and reject follow the below steps:

  • Open the Table’s property pane, and add click on the add new custom column, set the label to Approve and column type to Button.

Now create a new API from the Sheets Expense Manager data source and rename it to aprroveReimbursement, the idea here is to update the status of reimbursement whenever the approve button is hit on the Table. Now, in the query set the method to Update sheet row and row object to the following:

{
    "rowIndex":{{Table1.selectedRow.rowIndex}},
    "Status": "Approved"
}

Similarly, create another query for rejecting the reimbursement (rejectReimbursement), use the following code snippet in the Row Object property:

{
    "rowIndex":{{Table1.selectedRow.rowIndex}},
    "Status": "Rejected",
    "commentsInput":"{{commentsInput.text}}"
}

Now, go back to the Table Widget, and set the Approve button onClick property to execute a query and select the aprroveReimbursement query.

For rejecting the query select the rejectReimbursement on the Reject button’s onClick property.

Similarly, add two more tables to show all the approved and rejected reimbursement for future references, drag and drop two table widget’s and set the Table Data to the following:

Table2, Table Data:

{
    {{getReimbursement.data.filter((item) => {
  return item["Status"] == "Approved";
})}}
}
{
   Table3, Table Data:
{{getReimbursement.data.filter((item) => {
  return item["Status"] == "Rejected";
})}}
}

Add some additional metrics to track expenses!

Our dashboard is almost ready, now to give the admins an overall picture of expenses, let’s add some metrics that’ll help quickly look at the pending, paid, and rejected amounts. For this, we’ll need to drag and drop the text widgets and use the following snippet to do the calculations from the table widget.

In this code snippet, we’re iterating over the Table1 data and counting the summing up the Amount column. As the text widget accepts a string, the function will be returning the same data type. Similarly, let’s drop two more text widget’s and calculate the total amount approved and total amount rejected.

Use the following snippets:

Total Amount Pending

Text Widget Value:

Total Amount Rejected

{
 {{
function(){
    let count =0;
    for(let i=0;i < Table1.tableData.length; i++){
        count = count+ parseInt(Table1.tableData[i]["Amount"])
    }
        return "Total Amount Pending:  " + count
}()
}}
}

Now, finally, this is how the admin view looks like:

CleanShot 2021-09-03 at 13.20.45@2x.png

Building this app from scratch, including writing snippets of code is likely to take 30 minutes! Isn’t that simple?

If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like. Write to me at vihar@appsmith.com

Building an Expense Manager for your Startup!

Share this

From startups to large multinational corporations, every organization needs a tool or an application to keep a track of expenses. These apps usually help different departments, for example, the CEO’s office or finance teams to keep track of salaries, reimbursements and other company expenses.

There are several tools that one can find to manage expenses, however, these are either expensive or lack some crucial features that do not serve a large number of use-cases and finance goals.

We think that building expense managers shouldn’t be too complicated. So, we built an expense management dashboard using Appsmith and Google Sheets, which can be used by an organisation to manage reimbursements expenses based on their monthly budget limitations. Additionally, admins of the app can also either approve or reject it based on the reimbursement request that is raised by an employee.

Here’s a sneak peek of what the app looks like.

CleanShot 2021-09-03 at 13.20.45@2x.png
Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets, including charts, tables and forms, for building a UI fast.

Following are the table of contents:

  • Getting Started with Appsmith and Gsheets
  • Fetch Data from Google Sheet to Appsmith
  • Posting Data for Requesting Reimbursement
  • Expense Manager Admin
  • Adding some additional metrics to track expenses!

Set Up Google Sheets and Appsmith

Follow the steps below to use Google Sheets with Appsmith:

  1. Create a new account on Appsmith (it’s free!), if you are already an existing user, log in to your Appsmith account.
  2. Create a new application by clicking on the Create New button under the Appsmith dashboard.
  3. We’ll now see a new Appsmith app with an empty canvas and a sidebar with Widgets, APIs and DB Queries.
  4. Click on the + icon next to the APIs section and choose the Google Sheets option.
  5. Next, click on the New Datasource button and set the scope to Read and Write and click Authorise.
  6. This will ask us to log in from our Google Account, choose the account we want to access Google Sheets with and log in. After successful authorisation, this will redirect back to your Appsmith account.
  7. Now, you’ll find your Google Sheets Datasource under your APIs, and you can create the necessary queries by choosing this data source.

Awesome! Now that our Google Sheets Plugin is set up, let’s create a new Google Sheet and add the necessary fields required for managing expenses.

Following are the fields we’ll be considering:

Date
Requested On
Type
Reason
Amount
Status
Requested By
Attachment(s)
Comments from Admin

To make this more precise, we’ve made a sample Google Sheet with some mock data here. We’ll be using the same Sheet throughout this guide, and you can either follow with this or create your own based on our requirements.

docs.google.com/spreadsheets/d/1b7BuwDx0He4..

Fetch Data from Google Sheet to Appsmith

Now that we are connected to our Google Sheets data source, let’s connect to our Google Sheet and query all the data onto a list widget in Appsmith. To do this, navigate to the created data source under the APIs section and click on the New API button on the top right. Next, follow the below steps:

  • After clicking the New API button, you’ll be redirected to a new query tab, name your API to getReimbursement by double-clicking on the existing one.
  • Now set the method to Fetch Sheets Row, this method will query the data that’s present in the given Google Sheet.
  • In the SpreadSheet URL property, paste the URL of your Google Sheet, in this tutorial we’ll be following with the following URL:

https://docs.google.com/spreadsheets/d/1b7BuwDx0He41wtKYazxX3uJyIgQDHDRWyfyycL6mLMk/edit#gid=0

  • Next, in the Sheet name, add the name of our first sheet which is Reimbursement
  • The Table Heading Row Index property takes in the row number that basically has our column heading, we’ll set this to 1.
  • Next, we’ll set the Row Offset to 1 and Row limit to 100, this will query only the first 100 rows of our Google Sheet.
  • Lastly, click on the Run button on the top right, we should see the sheet information in JSON format in the response pane at the bottom.

Awesome, now that we have our data from the Google Sheet, let’s put this in a list; drag and drop a new table widget and paste the following in the Table Data property:

{{
    getReimbursement.data.filter((item) => {
        return item["Requested By"] == appsmith.user.name;
    })
}}
Quick Hack: For making the application more readable, let’s change the colour of the text based on the Reimbursement status. Appsmith allows us to write JS for styling widgets, now open the Text3 property pane and toggle JS in the text colour property and paste the following code:
Text1: {{currentItem.Reason}}
This will display the reason from the sheets row onto the list item

Text2: {{`Amount: ${currentItem.Amount} $`}}
Display’s the amount of the expense

Text3: {{`Status: ${currentItem.Status} $`}}
Display’s the status of the expense from the sheets row onto the list item

Text4: {{currentItem["Date"]}}
Display’s the status of the expense from the sheets row onto the list item

Text5: {{`Type: ${currentItem.Type}`}}
Display’s the Expense type from sheet

Text6: {{currentItem["Date"]}}
Display’s the date from the sheet

With this, our list widget is colourful and feature-rich.

Here’s how it looks like:

Post Data for Requesting Reimbursement

In this section, we’ll create a new form that will allow users to post new reimbursement requests to the admins. For this, let’s use the Form and Input components and Google Sheets integration on Appsmith. Follow the below steps:

  1. First, let’s add a container component to wrap all the widgets on the canvas.
  2. Drag and drop the Form widget onto the container widget, here we’ll see two button’s that’ll allow to submit a form and reset a form.
  3. Add the following widgets onto the form widget that’ll allow us to collect all the data to request a reimbursement.
  4. Select Widget (Name it as claimType)

We’ll add the select widget onto the form to set the type of reimbursement. Open the property-pane of the select widget and let’s add the options by pasting the following code:

{{
function () {
      let skills = ["Travel", "Hotel and Accomodation", "Food", "Medical", "Telephone","Fuel", "Imprest", "Other"];

      let options_list = skills.map((item) => {
        return {
          label: item,
          value: item,
        };
      });

      return options_list;
    }()
}}

Here we define an array of all the options and iterate through a for-loop and render them all in the select widget.

  • DatePicker Widget (Name it as expenseDate) The date picker widget allows us to add the date of the expense. To set the default date, open the property pane and set the Default Date to moment.now()
  • Input Widget (Name it as expenseDetail) Allows users to add the details of reimbursement.
  • Input Widget (Name it as expenseAmount) Allows the user to add the amount that’s requested for the reimbursement. Here, to make it more precise, ser the input type to float, to accept decimals in the given input.

Now that we have our form ready, let's write the logic to push the values from the form to the Google Sheet whenever submitted. Follow the below steps:

  • Create a new API from the existing Google Sheets data source and set the query method to Insert Sheet Row. This query method helps us insert new data to the Google Sheet from Appsmith.
  • Name the query as postReimbursement
  • Next, in the SpreadSheet URL property, add the link to our Google Sheet.
  • The Sheet Name will be Reimbursement as named in our Google Sheet.
  • The Table Heading Row Index will be the row where the names of the columns are listed; in our case, this will be 1,
  • Lastly, the Row Object takes a JSON object with keys set to the column names associated with the desired values. In our case, below is how the Row Object should look like:
{
    "Date": "{{expenseDate.formattedDate}}",
    "Requested On":"{{moment().format("L")}}",
    "Type": "{{claimType.selectedOptionValue}}",
    "Reason": "{{expenseDetail.text}}",
    "Amount": "{{expenseAmount.text}}",
    "Status": "Pending",
    "Requested By": "{{appsmith.user.name}}"
}

Here, the keys are the column names in the Google Sheet, and the values associated with them are the names of the input widgets. The .text method is used to pick the text that's written in the input widgets.

Lastly, in the form below for the submit button, set the on click property to Call an API and call the postReimbursement query from the options. With this, we should be able to add new rows to Google Sheet from Appsmith.

In the next section, we will build an Admin Dashboard where admins can approve or reject the requested reimbursement.

Expense Manager Admin

The expense manager admin can be only viewed by the admins of the application. Here we’ll be displaying all the information regarding the reimbursement’s and add an option to approve and reject them with a comment.

To build this, follow the steps below:

  1. Create a new page by clicking on the + icon next to the pages and rename it to Admin View.
  2. Add a container widget across the canvas to group all the widget’s Copy the getReimbursement query from Page1 to Admin View page
  3. We’ll now add a table to show all the pending reimbursements and add an option to approve and reject them. Drag and drop a table widget onto the container, open the property pane and set the Table Data to the following:
{{getReimbursement.data.filter((item) => {
  return item["Status"] == "Pending";
})}}

With this, we should see all the reimbursement requests with pending status on the table widget. Now, add to add options to approve and reject follow the below steps:

  • Open the Table’s property pane, and add click on the add new custom column, set the label to Approve and column type to Button.

Now create a new API from the Sheets Expense Manager data source and rename it to aprroveReimbursement, the idea here is to update the status of reimbursement whenever the approve button is hit on the Table. Now, in the query set the method to Update sheet row and row object to the following:

{
    "rowIndex":{{Table1.selectedRow.rowIndex}},
    "Status": "Approved"
}

Similarly, create another query for rejecting the reimbursement (rejectReimbursement), use the following code snippet in the Row Object property:

{
    "rowIndex":{{Table1.selectedRow.rowIndex}},
    "Status": "Rejected",
    "commentsInput":"{{commentsInput.text}}"
}

Now, go back to the Table Widget, and set the Approve button onClick property to execute a query and select the aprroveReimbursement query.

For rejecting the query select the rejectReimbursement on the Reject button’s onClick property.

Similarly, add two more tables to show all the approved and rejected reimbursement for future references, drag and drop two table widget’s and set the Table Data to the following:

Table2, Table Data:

{
    {{getReimbursement.data.filter((item) => {
  return item["Status"] == "Approved";
})}}
}
{
   Table3, Table Data:
{{getReimbursement.data.filter((item) => {
  return item["Status"] == "Rejected";
})}}
}

Add some additional metrics to track expenses!

Our dashboard is almost ready, now to give the admins an overall picture of expenses, let’s add some metrics that’ll help quickly look at the pending, paid, and rejected amounts. For this, we’ll need to drag and drop the text widgets and use the following snippet to do the calculations from the table widget.

In this code snippet, we’re iterating over the Table1 data and counting the summing up the Amount column. As the text widget accepts a string, the function will be returning the same data type. Similarly, let’s drop two more text widget’s and calculate the total amount approved and total amount rejected.

Use the following snippets:

Total Amount Pending

Text Widget Value:

Total Amount Rejected

{
 {{
function(){
    let count =0;
    for(let i=0;i < Table1.tableData.length; i++){
        count = count+ parseInt(Table1.tableData[i]["Amount"])
    }
        return "Total Amount Pending:  " + count
}()
}}
}

Now, finally, this is how the admin view looks like:

CleanShot 2021-09-03 at 13.20.45@2x.png

Building this app from scratch, including writing snippets of code is likely to take 30 minutes! Isn’t that simple?

If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like. Write to me at vihar@appsmith.com

What’s a Rich Text element?

The rich text element allows you to create and format headings, paragraphs, blockquotes, images, and video all in one place instead of having to add and format them individually. Just double-click and easily create content.

  • xvcmbmvkmnkmbknmbkmlnj
  • A rich text element can be used with static or dynamic content. For static content, just drop it

A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!

  1. A rich text element can be used with static or dynamic content. For static content, just drop it into any page and begin editing. For dynamic content, add a rich text field to any collection and then connect a rich text element to that field in the settings panel. Voila!

How to customize formatting for each rich text

Headings, paragraphs, blockquotes, figures, images, and figure captions can all be styled after a class is added to the rich text element using the "When inside of" nested selector system.

ksnopirirfnb [aorewmb[oiewsn b[opebr
  1. then connect a rich text

dfbstjsrykmsry

Square
Try Appsmith
How to build a GitHub issues dashboard in thirty minutes or less
26
October
2022
Tutorial

How to build a GitHub issues dashboard in thirty minutes or less

How to build a GitHub issues dashboard in thirty minutes or less
Joseph Petty
0
 minutes ↗
#
graphql
#
github
Tutorial

GitHub Insights provides totals on issues and pull requests including links to  individual issues and PRs. You can filter by the last day, three days, a week or a month. It’s like a dashboard overview of your GitHub repository.

GitHub_dashboard__Appsmith.jpg (1920×1080)

While this is useful, it lacks a few key features.

  • It can’t have customized views for insights unique to you and your team.
  • It doesn’t drill-down into sub-categories of like issues by type or most popular issues by the number of comments.

To get around, this two-part post will show how to build a custom dashboard for your team and repos using GitHub’s GraphQL endpoints and native-GraphQL support in Appsmith.

Sign up to build your own GitHub insights dashboard

What this part covers

In two words, GraphQL and JavaScript. You will see how to:

  • Enable GitHub’s GraphQL API for your repo and create an access token
  • Build queries with the GitHub GraphQL explorer
  • Use GraphQL query variables
  • Use the authenticated GraphQL API in Appsmith
  • Present the response data on a dashboard

  • Use the Array map() method in JavaScript

Enabling the GitHub API

From your GitHub apps settings,

  1. Select Personal Access Token, and then Generate a new token.
  1. Add a note, set an expiration date, and select the scopes you want to enable. For our dashboard, we’ve left all the write scopes unchecked because the dashboard is read-only.
  1. Click Generate Token and then copy and save the token somewhere safe. You won’t be able to access it again from GitHub.
GitHub_apps_settings__Appsmith.jpg (1920×1080)

Building queries with the GitHub GraphQL explorer

  1. Start by logging into the Explorer to access the GraphQL API. You should see a default query already entered. Run it to make sure the Explorer can access your GitHub account.
GitHub_GraphQL_explorer.jpg (1920×1080)

{
  "data": {
    "viewer": {
      "login": "YOUR_GITHUB_LOGIN"
    }
  }
}

You should get back a response with your GitHub username.

  • Click the Explorer button next to show the left sidebar, then build a new query to get a few top level stats about your repo. Just go through the >repository node and select any fields you want to include in the dashboard.
  1. Type in the name of the repository and the owner name, then run the query.

{
  viewer {
    login
  }
  repository(name : "appsmith", owner : "appsmithorg") {
    id
    description
    homepageUrl
    stargazerCount
    issues {
      totalCount
    }
  }
}

You should see the totalCount populate as a run-time response for the query.
  1. You now have the totalCount of issues but not the breakdown by Open and Closed. While these would be two different queries in the REST API world, GraphQL lets you use aliases in one query to get subsets of the overall data.
Nice!

GraphQL query variables

  1. Instead of hard coding the repo name and owner, you can use query variables from the QUERY VARIABLES pane as an object.

{
  "owner": "appsmithorg",
  "repo": "appsmith"
}

  1. You would then add the variables to the original query.

query MyQuery($repo:String!,$owner:String!) {
  repository(name: $repo, owner: $owner) {
    issuesOpen:issues(states: OPEN) {
      totalCount
    }
    issuesClosed:issues(states: CLOSED) {
      totalCount
    }
  }
}

  1. Go through the rest of the >repository node of the Explorer and add any other fields you’d like for your dashboard, using aliases where needed to get subtotals. The final result should look something like ↓.

query getOVerview($owner:String!, $repo:String!) {
	repository(name: $repo, owner: $owner) {
		id
		name
		owner {
			id
			login
		}
		description
		homepageUrl
		stargazerCount
		collaborators {
			totalCount
		}
		watchers {
			totalCount
		}
		forkCount
		diskUsage
		issues_open: issues(states: OPEN) {
			totalCount
		}
		issues_closed: issues(states: CLOSED) {
			totalCount
		}
		PRs_open: pullRequests(states: OPEN) {
			totalCount
		}
		PRs_merged: pullRequests(states: MERGED) {
			totalCount
		}
	}
}

  1. While you’re here, create one more query to get a list of repos for your GitHub Organization. This will be useful in offering a picklist of repo names to choose from later.

query {
  organization(login: "appsmithorg") {
    repositories(first: 100) {
      edges {
        node {
          name
        }
      }
    } 
  }
}

GitHubs_name_attribute_on_GraphQL_explorer__Appsmith.jpeg (1920×1080)

Connecting the GraphQL API in Appsmith

Now that you have your GraphQL API token and the queries for your dashboard, let’s set up an authenticated GraphQL API in Appsmith.

  1. Start a new app, then add a new datasource from the Explorer in the left nav.

URL: https://api.github.com/graphql

Authentication Type: Bearer Token

Bearer: {{value from GitHub}}

  • The Bearer Token value is they access token you generated on Github.
  1. Name and save the datasource.
  1. Add a new API to this datasource.
  1. Copy the query from GitHub’s GraphQL Explorer into the Body of the API.
  1. Copy the query variables from GitHub to the Query Variables pane.
  1. Run the API.
GitHub_GraphQL_query_response__Appsmith.jpg (1920×1080)
  1. Add one more API for the repo list, and copy over the query and variables from GitHub.
Alright! We have the API playing nice with Appsmith.

Presenting data in widgets

First add an Input Widget for the owner name.

💡 Smith’s tip

Give it a descriptive name you can reference uniquely later.

I prefer using a short prefix for the widget type, e.g., in (for input), followed by an underscore, and then a descriptive term for the data it presents, e.g., in_owner. When I dynamically need the text value in the widget, I can reference it with {{in_owner.text}} in queries, JS toggles on widget props, or JS Objects in Appsmith.

Next, add a Select Widget for the repository name. With this widget, the user’s input can be referenced as {{in_repo.selecteOptionValue}}.

  1. The Select Widget requires an array of key-value pairs denoted label and value to supply the picklist’s options. If you look closer at the API response, you see a list of repo names but the data isn’t structured for this widget yet.
GitHub_GraphQL_repo_list_data_isnt_structured_for_Select__Appsmith.jpg (1920×1080)

Enter JavaScript

  1. First, let’s map over the edges and return an array of the nodes.
.map()_to_get_the_nodes_in_key-value_pairs__Appsmith.jpg (1920×1080)
  1. Then map over the nodes array to return the {label, value} array expected by the Select Widget.

nodesToArray: ()=> {
		return getRepos.data.data.organization.repositories.edges.map(e=>e.node)
			.map(n=>({label:n.name,value:n.name}))
}

Neat!
  • This lets you bind the data to the Select widget so when the end-user selects a repo name from the picklist, the query to the API can automatically be triggered.
Binding_the_transformed_name_node_array_to_Select__Appsmith.jpg (1920×1080)
For the Default Selected Value, you can use [0].value to default it to the first option in the picklist which is the first option in the query response.
  1. In the Select widget’s properties, set the onOptionChange event to trigger the query run the the getOverview API automatically for each option selected.
onOptionChange_binding_on_Select__Appsmith.jpg (1920×1080)
  1. Edit the getOverview API query variables to reference the new widgets. You can also add a fallback value in case the input is blank.

{
  "owner": {{in_owner.text || 'appsmithorg'}},
  "repo": {{in_repo.selectedOptionValue || 'appsmith'}}
}

The ||, the symbol for the logical OR operator, is to declare a default if the repo name is blank.

  1. Next up? Choosing the right widget for the data returned by the API. This part needs some understanding of the data and its structure. Here’s what the query response looks like.

//getOverview.data:
{
  "data": {
    "repository": {
      "id": "MDEwOlJlcG9zaXRvcnkyNzU5OTM4ODU=",
      "name": "appsmith",
      "owner": {
        "id": "MDEyOk9yZ2FuaXphdGlvbjY3NjIwMjE4",
        "login": "appsmithorg"
      },
      "description": "Low code project to build admin panels, internal tools, and dashboards. Integrates with 15+ databases and any API.",
      "homepageUrl": "https://www.appsmith.com",
      "stargazerCount": 21504,
      "collaborators": {
        "totalCount": 113
      },
      "watchers": {
        "totalCount": 232
      },
      "forkCount": 1849,
      "diskUsage": 286069,
      "issues_open": {
        "totalCount": 2579
      },
      "issues_closed": {
        "totalCount": 7458
      },
      "PRs_open": {
        "totalCount": 87
      },
      "PRs_merged": {
        "totalCount": 5893
      }
    }
  }
}

One way could be having a widget for each data point but that could very easily get out of hand if you have hundreds of fields. We will keep the Appsmith spirit of easy and fast and choose something more thoughtful.

  1. Let’s try the Table
  1. How about a JSONForm?
Presenting_GitHub_GraphQL_data_on_a_JSONForm__Appsmith.jpeg (1920×1080)
Better, but the data is still spread out and it’s hard to see everything at once.

We need a pivot-table of the data, but the current JSON structure isn’t quite right for that. So, we'll transform the data on the fly.

More JavaScript

  1. Your dashboard building trip so far has been without code. To build the UI right for the data, though, you will need to enter coding territory and introduce this data to our good friend, JavaScript.

1. Object.keys()

  1. Instead of one horizontal row, what if we could have one column for the field names and one for the values? The new data format should look something like ↓.

[
  {
    "field_name": "id",
    "value": "MDEwOlJlcG9zaXRvcnkyNzU5OTM4ODU="
  },  
  {
    "field_name": "Name",
    "value": "appsmith"
  },
...
]

  1. Start with a function that returns a list of the fields, or properties, of the repository object.

objToRows: (obj=getOverview.data.data.repository) => {
		return Object.keys(obj)
	},

🧠 Use Object.keys(object) to return an array of property names from an object.

2. Reduce

Next, you will need to iterate over these properties and return a new row object for our pivot table. The end result should be an array of objects with two properties, one for the field name and one for the value.

Sounds like a job for the reduce method.

Start with an empty array and add a new object for each property in the Object.keys() array.


objToRows: (obj=getOverview.data.data.repository) => {
		return Object.keys(obj).reduce((acc, value) => acc.concat({ 'prop': value, 'value': obj[value]}), [])
	}

Populating_(obj)_with_reduce__Appsmith.jpg (1920×1080)
  1. Next up? Choosing the right widget for the data returned by the API. This part needs some understanding of the data and its structure. Here’s what the query response looks like.

Presenting this new and transformed data on the Table should look like ↓.

Presenting_GitHub_GraphQL_transformed_data_on_the_Table__Appsmith.jpeg (1920×1080)
Cleaner UI, easier to see everything at once, but it could use some formatting.
  1. Your header row is a column now—magic!—, so it should look better to format the first column differently.

☝🏾 Disabling search, filters, download, and pagination options will help clean up the UI further. You don’t need those options for your dashboard anyway.

totalCount_in_GitHub_GraphQL_data_attributes__Appsmith.jpg (1920×1080)
Not bad, but it could be better. You’re building a dashboard after all.

3. Ternary operators

Those nested totalCount fields are kinda ugly. It would be much better if each value in the value column showed only the count. Before you get to doing it dynamically, let’s walk through the logic in plain English first.

If the value in the value column is an object and it has only one property,  show only the value for that property.

Use typeof to see if the value is an object, then use Object.keys().length to see if there is only one property.

Code logic

if ( typeof value == 'object' && Object.keys(value).length==1 ) ...do something

If the value in the value column is an object and it has nested properties, you want to show only the value of the nested properties, else you want to return whatever value was already there.

Use a ternary operator for this logic.

Code logic


flattenValues: (rows=utils.objToRows()) => {
		rows.forEach(r=>r.value = typeof r.value == 'object' && Object.keys(r.value).length==1 ? r.value[Object.keys(r.value)[0]] : r.value  );
		return rows
	}

Query_response_after_a_ternary_transformation__Appsmith.jpg (1920×1080)
Looks nice, but what about on the Table?
Data_on_table_minust_tableCount_labels__Appsmith.jpg (1920×1080)
Et voila!
GraphQL’s claim to fame is a lot fewer API calls than REST. As you have seen so far, the dashboard for the repo of interest is a single API call. You can continue tweaking the request body to match the exact data you need for the UI to get all of it in a single API response.

For finishing touches, move the in_repo and in_owner widgets into a new container for the header and add a second container for the Table.

Final_looks_in_containers__Appsmith.jpg (1920×1080)

Finally, add a border color and a width of 1.5 to make it look nice.

Border_and_color_for_the_GitHub_GraphQL_dashboard_MVP.jpg (1920×1080)
This dashboard has legs. It will run and run.

So, you now have an access token from GitHub, a ready GraphQL endpoint plugged into Appsmith, JavaScript-transformed data, and a pretty decent UI for it. ! And we're not even 15 minutes in! 

In part two, you will build the Issues Dashboard with a breakdown of issues by type over a time period of your choice, get issues with the most comments, and more.

Stay tuned.

The Low-coders Guide To JavaScript
19
October
2022
Product

The Low-coders Guide To JavaScript

The Low-coders Guide To JavaScript
Apeksha
0
 minutes ↗
#
javascript
#
js
#
code
Product
Write quick, clean, powerful code to build functional and beautiful internal apps

Appsmith falls squarely on the side of low-code in the low-code-no-code fence. We offer you the flexibility to write code when you want to. In keeping with the spirit of low-code, we also make it easier than the heavy-code writing experience without trading off the God Mode-powers you are used to.This post maps the different ways and places you can write code on Appsmith to scenarios where they are most useful. You will learn how you can use JavaScript

  • to present data in and play with data from widgets
  • transform data for your and your end-users’ needs
  • conditionally execute code to achieve unique end-user experiences
For our example, we will see how to build a dashboard for historical analytics of the Olympics, connecting to data on a PostgreSQL database.

Present data right

While our widgets can present data with assisted query bindings, Javascript makes it easier to bind transformed data per a widget's expected datatype.

Let the bindings begin

Post_1.gif (854×480)
One small step in the code, one giant leap for your app

Assuming you have connected your database to Appsmith and have run a successful query like we have, you can now present the query’s response using JavaScript with any widget, passing the .data operator to the query.

Snippet used


{{fetch_athelets.data}}

Methods to the madness

As you build your app, you will often need to show data differently from how it appears in the query response an consequently, the widget. JavaScript methods, natively supported in Appsmith, help make this a breeze.

  • map.(), used directly in the binding on a widget’s data property, helps show only those row values that match data attributes as defined in the map.() method.

Snippet used


{{
fetch_athelets.data.map((item) => {
      return {
				id: item.id,
        name: item.name,
        team: item.team,
        sex: item.sex,
        season: item.season,
				sport:item.sport
      };
    });
}}

Post_2.jpg (1920×1080)
map.() shows the values for just the names of players, the team they belong to, the season, and the sport.
  • Like map.(), you can use the .filter() method in a widget’s query bindings to refine the data presented.

Snippet used


{{
fetch_athelets.data.map((item) => {
      return {
				id: item.id,
        name: item.name,
        team: item.team,
        sex: item.sex,
        season: item.season,
				sport:item.sport
      };
    }).filter((item) => {
        return item.season == "Summer";
      });;
}}

Post_3.jpg (1920×1080)
Using filter.() atop map.() lets us show the data for just the Summer Olympics.
🥳 Auto-complete contextually assists in picking supported methods, eliminating guess-work and repetitive code writes.

Objectifying data

The Table, as we have seen so far, expects data in the Array<Object> format. The Select widget, though, expects data in the Array<{ "label": "string", "value": "string" }> format. This format or datatype is called an object.

Snippet used


{{

fetch_seasons.data.map(i => ({
	label: i.season,
	value: i.season
}))

}}

Post_4.gif (854×480)
Our app now has two simple filters.

Functions on the run

The Select also expects unique values and throws a fit when you have duplicate values in your records. To show unique picklist options, use IIFEs and remove duplicates in the data binding.

🏃 An Immediately Invoked Function Expression (IIFE) is a JavaScript function that runs as soon as it is defined.

Snippet used


function make_unique() {
  let data = fetch_seasons.data.map((i) => ({
    label: i.game_season,
    value: i.game_season,
  }));

  const key = "label";
  const arrayUniqueByKey = [
    ...new Map(data.map((item) => [item[key], item])).values(),
  ];
  return arrayUniqueByKey;
}()

CleanShot_2022-10-25_at_15.55.292x.png (3584×2082)

Make your queries smarter

Pro devs will recognize we have taken simpler, non-smart routes to present data thus far. In reality, there are browser performance considerations and limitations to using methods or writing just about any code on the full query response.Using Appsmith to optimize queries and thus, your app’s performance, is easy.

Dynamism

A smarter way to present filtered data is to bake in dynamic triggers per action taken on widgets in the query itself irrespective of the query language. Because, JavaScript. 🤯 You can do that using {{ }} in Appsmith.

Snippet used


SELECT name, sex, age, team, year, season, city, sport, event, medal
FROM public."athelete"
WHERE
	season = {{filter_season.selectedOptionValue}}
ORDER BY year LIMIT 100;

Post_5.gif (854×480)
We write a SQL query that includes a JavaScript snippet under the WHERE clause. filter_season is the name of the Select widget and selectedOptionValue is the property that we bind to the query.

Refreshed and informed

Now that you have the filtered data from your smarter query, you will need to provision for data refreshes triggered by widgets. For example, if your users select an option from the Select widget, whichever widget presents the data—in this case, the Table—needs to now present the filtered view.

This is super-easy with Appsmith, thanks to JavaScript in widget props.

  • Properties like onChange and onClick can take JavaScript to refresh the data in a widget specific to an end-user action.
Post_6_(1).gif (854×480)
onChange and onClick take {{ fetch_athelets_with_filters.run() }} to refresh the data by the Select widget’s options.

  • Additionally, you can also show alerts on your app’s UI to inform your end-users of successful query execution.

Snippet used

{{ fetch_athelets_with_filters.run( () => showAlert( "Showing data from " + filter_season.selectedOptionValue + " season", "success" ), () => showAlert("Query failed with an error", "error") ); }}
Call confirmed?

Transformed

For scenarios where you need to show data from different rows or even entirely different tables on your database, the Appsmith Query Editor assists with JavaScript methods.

  • Add multiple dynamic conditions from widgets and transform your query response with JavaScript methods.

Snippet used


SELECT name, sex, age, team, year, season, city, sport, event, medal
FROM public."athelete"
WHERE
	season = {{filter_season.selectedOptionValue}} and
	year IN {{ filter_year.selectedOptionValues.join(',') }} and
	sport = {{filter_sport.selectedOptionValue}}
ORDER BY year LIMIT 100; 

Post_8.jpg (1920×1080)
The Evaluated Value pane—we really a better name for it—resolves JavaScript methods like .join to help see what a response may look like.

  • Auto-complete helps you select supported functions within {{ }} so you don’t feel the need peek at StackOverflow.
Post_9.jpg (1920×1080)

Make your code performant

Appsmith comes with a few JavaScript libraries out of the box to help you offer desired features and performance to your end-users without code circus. Use them inside {{ }}, or JS Objects, just like any other binding or transformation.

Lodash for the win

Lodash helps you write performant JavaScript with less code. Use _. to invoke Lodash methods anywhere you write JavaScript in Appsmith.

  • Remove duplicate values using the _.uniqWith, and _.isEqual from Lodash inside your bindings.

Snippet used


{{
    _.uniqWith(fetch_events.data.map((i) => i.sport))
      .map(
        (i) => ({
          label: i,
          value: i,
        }),
        _.isEqual
      )
}}

Post_10.jpg (1920×1080)
A binded query shows values by the unique names of sports in the Olympics.

  • Use _union(), to add more items to your array.

Snippet used

{{ _.union( _.uniqWith(fetch_events.data.map((i) => i.sport)).map( (i) => ({ label: i, value: i, }), _.isEqual ), [{ value: "All", label: "All" }] ); }}
Post_11.jpg (1920×1080)
An All option is now present in our Select widget’s list of options.
  • Sort your data with _orderBy.

Snippet used


{{
    _.orderBy(
      _.union(
        _.uniqWith(fetch_events.data.map((i) => i.sport)).map(
          (i) => ({
            label: i,
            value: i,
          }),
          _.isEqual
        ),
        [{ value: "All", label: "All" }]
      ),
      ["label"],
      ["desc"]
    );
}}

Post_12.jpg (1920×1080)
We automatically sort the filtered data alphabetically.

Moment to moment

When working with dates and times, moment comes handy. For example, you can use moment.js to show the countdown between two dates.

Here’s a Moment.js cheatsheet to keep handy.

Snippet used

The next Olympics starts on Friday, 26 July 2024 (Countdown-- {{function(){ // convert dates into required formats let today = moment().format("LL"); let next_olympics= moment("Friday, 26 July, 2024") return next_olympics.diff(today, 'days'); }()}} - days)
Post_13.jpg (1920×1080)
We use moment.js to show when the next Olympics start.
Today, Appsmith supports six external libraries. Learn more.

Extend code functionality

For conditional navigation to another page in your app, opening and closing interactive elements like modals, showing alerts, and storing the value of a variable for dependent use later, Appsmith offers Actions in JS Objects.

➡️ Ten action methods with the Appsmith framework will make it easy for you to write better code within your application. Read about Actions on docs here.
  • Store any value within your application's local session store using the storeValue function.

Snippet used


{{
    storeValue("selected_year", filter_year.selectedOptionValue).then(() => {
      showAlert("Saved selected to local store");
    });
}}

Post_16.gif (854×480)
We store the year selected so we can use it later for a chart. The JS Object also includes a showAlert function to inform the selected year was stored successfully.
  • Switch between pages using the navigateTo action referencing a JS Object…

Snippet used

{{ storeValue("selected_year", filter_year.selectedOptionValue).then(() => { navigateTo('Page2', 'SAME_WINDOW') }) }}
  • …or just between tabs.

Snippet used

{{ storeValue("selected_year", filter_year.selectedOptionValue).then(() => { storeValue("selected_tab", "Analyse by year") }) }}
Post_18.gif (854×480)

Writing clean code

JavaScript toggles in widget props and in queries are great, but sometimes, you need to reference longer JavaScript code when building your app. JS Objects on Appsmith offer you a modern JavaScript Editor experience for writing your JavaScript functions more cleanly and efficiently.

Declare reusable functions and variables

To avoid referencing variables over and over in your queries, you can just write a JS Object with functions and variables and reference the Object in your queries.

  • For queries that need to run in sequence, a JS Object helps write cleaner dependencies and asynchronous functions.

Snippet used

onSeasonChange: async () => { return fetch_years.run().then(() => { return fetch_events.run(); }) .then(() => { return fetch_athelets_with_filters.run(); }); }
Post_14_(1).gif (854×480)
Selecting “Winter” updates the values in the adjacent filters relative to the season.

  • Use async and await inside JS Objects to enable promise-based asynchronous workflows.

Snippet used

refresh: async () => { await fetch_seasons.run() await fetch_years.run(); await fetch_events.run(); await fetch_athelets_with_filters.run(); }
Post_15.gif (854×480)
Our default season is “Summer”, so each time we hit the Refresh button, the JS Object first sets the default season to Summer, and then sets default values for the Year and Sports filters.

Chart it out

Assuming you are already familiar with Appsmith Charts and how to bind data to them, you can use JS Objects to configure your chart axes, labels, and data fields, then reference it directly within the chart widget’s props with something like {{chart_manager.events_by_sports()}}.

Snippet used


events_by_sports: () => {
		// bring data from query
		const data = chart_event.data;
		const dataSource = {
			
			// fusion chart configuration for customizing charts
			chart: {
				xaxisname: "Sport",
				yaxisname: "Number of events",
				palettecolors: "#818cf8",
				theme: "fusion"
			},

			// query transformations with .map()
			data: data.map(e => ({
				label: e.sport,
				value: e.event
			}))
		};
		return (
			{
				// chart type and configuration
				type: "column2d",
				dataFormat: "json",
				dataSource
			}
		);
	},

Post_21_(1).gif (854×480)
The year selected in storeValue is used to show the number of events by sport on a pretty chart. ❤️

Final touches

To add a pinch of personality to your app, you can use JavaScript in Text or other widgets’ props to show data from public APIs, another database, or even SaaS tools like Slack and Notion.

Our fun snippet at the bottom is a really fun use of JavaScript and is coded as Did you know, in {{year_filter.selectedOptionValue}}, a whopping {{chart_event.data[0].event}} events were conducted in {{chart_event.data[0].sport}}?.
Meet the Table: A React table for building internal apps in less than 30 minutes
7
October
2022
Product

Meet the Table: A React table for building internal apps in less than 30 minutes

Meet the Table: A React table for building internal apps in less than 30 minutes
Balaji Soundararajan
0
 minutes ↗
#
widgets
#
product
#
table
Product

The Table is one of our top widgets by usage. Even with 25% of our users sending us telemetry data today, it has been used more than 820,000 times since we launched it. 🤯

Built on React Table

There are several libraries for building React tables, including our favorite and the massively popular, the React Table. We like it so much we sponsor it and can’t thank Tanner enough for building it.

We built our Table on top of it to help build internal apps lightning fast with ready features to support most popular use cases—an admin panel, dashboards, monitoring apps, and more—with out-of-the-box support for styling, theming, and in-line editing.

Let’s get into it

This post tears the upgraded Table widget down to all its component features and how you can use them. While our example today is an admin panel for a MakerPad-clone on Postgres, you will see how to build an admin panel for any datasource or other apps like dashboards, portals, and even CMSes.

💡 Toolspace, our MakerPad-clone, is a discovery app for open-source projects built on Postgres and Django. The Appsmith admin panel runs CRUD operations to the Postgres database for categories, alternative projects, data, and users.

Present tabular data

The Appsmith Table is familiar with a few powerful differences right out of the gate to help you leapfrog over typical web table set-ups.

One binding to bring it all

Assuming you have already connected your datasource and set up get queries to fetch data, it takes one small change—a binding—your freshly dropped Table to present the data.

Post_A.gif (1024×576)

Easy views for your data

You can write JavaScript almost everywhere in Appsmith. This comes handy when you want to create a view of your data for your app. Extend your Table Data binding with something like ↓


{{
getAllProjects.data.map((item)=>{
			return {
				"id": item.id,
				"name":item.name,
				"slug":item.slug,
				"logo":item.image_url,
				"github_url": item.github
			}
		})
}}

and voila! Your Table now shows the view you want. Use the Table Data property to manipulate your views any time.

☝ For easier code editing, we recommend you write longer code in a JS Object and reference it in the Table Data property.

Data to Table to data

Data exists as text, images, videos, URLs, and others on your database. Easily map datatypes from your database from the Column Type property under each column’s settings. This mapping is also used to update your database, as you will see later in this post.

Post_B.gif (1024×576)
A link pointer appears on URLs, and the image URLs change to the actual logo. 😍

For binary state values in columns—true or false, really— the Checkbox or Switch column types make it easy and visual for your end-users.

👌🏽 The Table supports over ten column types, including interactive components like buttons and icons. We talk about them below.

All in the names

Your database may have column headers that aren’t the best for your users, e.g., github_url. Edit those headers right on the Table as you would a filename. Double-click, type in your preferred header, e.g., GitHub URL, for each column, and done.

Post_3_(1).gif (1024×576)
No changing bindings. No transformations for such a small change.

What’s the value

Sometimes, your data isn’t in a single-entry-per-cell format. In our example, the alternative_products column has several related items per tool, so the column shows up as [], indicating an array.

Computed Value under column settings for each column translates the value in one swift binding to show that number.


{{currentRow["alternative_products"].length}}

Post_D.gif (1024×576)
👉 Computed Value supports JavaScript methods and functions—all of them.

Peek-a-boo columns

Often, you find columns you need to build and run your app—e.g., slug, that end-users don’t need to see. One-click on the eye per column on the Property pane, and you are done making them invisible to your users.

👉 Scroll horizontally to find hidden columns after the last visible column when you are building your app. Your end-users don’t see them when you deploy the app, though.

Order, order

Rearranging columns to present information hierarchically to your end-users is just a simple drag-and-let-go thing under Columns on the Property pane.

Post_E.gif (1024×576)
We move Name, Description, and Logo to show-up first. Easy-peasy ✌️.

Prettify it

The Table recently got themed, so you can now choose a default or a custom theme for your admin panel and see the Table get a personality in one click.

Post_M.gif (1024×576)

Unlock higher degrees of personalization for pixel perfect matches to your brand guidelines.

  • Color cells by column values with conditional styling using single or nested conditions in JS toggles available under the Style tab inside column properties.
Post_13.gif (1024×576)

{{
function() {
  if (currentRow.alternative_products.length == 0) {
    return "#E87171";
  }
  if (currentRow.alternative_products.length == 1) {
    return "#84cc16";
  }
  if (currentRow.alternative_products.length > 1) {
    return "#3b82f6";
  }
}()
}}


  • Control row height—short, default, tall—, text size—small, medium, large, extra-large—, and text alignment—left, right, center, top,  bottom—by the number of items you want to show. No-code needed.
Post_N_(1).gif (1024×576)
  • Customize borders and shadows for the entire table for consistency with your brand styles.
Post_O.gif (1024×576)

Get your rows in a page

Showing all your rows in one endless scroll isn’t the best end-user experience and loading it is a chore for your browser.

Magically paginate your apps with two out-of-the-box methods in Appsmith.

  • The pageNo and pageSize properties simplify pagination out of the box for queries and APIs.
Post_F.gif (1024×576)
We just add a Tablename.pageNo param to the API. That’s it.

  • We just add a Tablename.pageNo param to the API. That’s it.
  • The response-URL method relies on server-side pagination and is simpler for endpoints that have paginated URLs.
Post_9.gif (1024×576)

Make your data accessible

Presenting the data isn’t enough, especially when you have more rows than the lines of code you have written so far in Appsmith.

The Table makes accessibility easy and delightful.

Wrap it up

Toggling the Cell Wrapping settling on under Column settings lets your end-users see long text and evaluated values of dynamic bindings without scrolling or double-click gimmicks.

Search for it

Tables in Appsmith come with pre-built Search for all your data, paginated or otherwise. Search runs automatically each time you enter or change a keyword in the Search box.

Easily configure it one of two ways.

  • Write a search query with keywords like  ilike or find depending on your database.

SELECT * FROM users WHERE name ilike '%{{Table1.searchText}}%'

  • Use query params dynamically with your API like ↓.

https://api.toolspace.dev/api/all-xyz/?search={{Table1.searchText}}}


Sort and filter

While sorting your data takes just clicking column names or headers, filters offer a little more power to you.

  • Select a column, add a filter condition, and done—just like a spreadsheet.
Post_G_(1).jpg (1920×1080)
  • Add AND,  OR conditions for advanced filtering—just like you would in an analytics tool.
Post_H_(1).jpg (1920×1080)

Get a good view

Build unique views for better data presentation with table properties and triggers.

Spreadsheet view -- Use tabs to display multiple tables or filtered views of your data.

Post_I_(1).gif (1440×810)

Details view -- Show detailed views of your data on off-table components like Modal, Input, and Text widgets.

  • onClick-trigger them with Buttons, Icons, or Menus in a column.
Post_J.gif (1024×576)
Each row can have a detailed view triggered by a button, icon, or menu.
  • Menu buttons are especially useful to nest both view and delete triggers in one component—good for when you want to let familiar end-users delete data easily.
Post_Q.gif (1024×576)
  • Nifty Appsmith-native properties like selectedRow, triggeredRow, and selectedRowIndex make it super easy for you to dynamically open details views and, as we’ll see later, even edit them when you swap out the Text widget for an Input widget.
Post_7.gif (1024×576)
  • Run update queries using buttons to perform edit operations.
Post_P.gif (1024×576)

Did we mention already you can set intervals for auto-data refresh? No? Well, ↓ is all it takes.


{{

// runs for every five seconds

setInterval(() => { 
	 getAllProjects.run()
}, 5000)
}}

Post_14.gif (1024×576)

Two-click data export

Exporting your table data is, like most things so far, an affair of clicks for two of the most popular types—CSV or Excel

Post_12.gif (1024×576)

Add-edit-delete your data

So far, we have built a pretty functional, pretty pretty data viewer. This last set of features turns a viewer into a full-blown admin panel.

  • Add to your data from views

Use a Modal + an Input widgets, triggered from a column type or a Button outside of the table to open a set of blank input fields that save data entered with an onClick trigger.

Post_U.gif (1440×810)
  • Edit from views

Remember the unique views you built for your data? Extend those views to make them editable with Input + Button widgets instead of the Text widget. All changes to your fields get updated with the Button’s onClick trigger binded to an update query.

Post_S.gif (1024×576)
  • Delete from the Menu

We configured a delete trigger in our Menus before. Let’s put that to good use.

Post_T.gif (1024×576)

And, drumroll, please, you have a full-blown admin panel in under half-an-hour! 🥳

But wait, what if…

Inline editing without inline code

…your awesome admin panel could let your end-users edit data like they would on a spreadsheet?

Inline editing does just that. With your query for updates to your datasource built, all you need is to,

  • make your columns editable, and
  • bind the query to the EditActions column type—shown as the onSave property.
Post_L.jpg (1920×1080)
  • Your users then just do ↓.

Where the Table’s going

The Table has come a long way since we launched it many, many release cycles ago. Little did we think it could potentially become a CRUD app builder on its own. Which it is quite nearly now. So, we are building more and more on the table ourselves and asking, “Can’t we really turn this into an end-to-end app builder?”

You are welcome to stay updated about our tries or even contribute, but don’t let either of those stop you from using the Table, say, for an admin panel to a directory or for powering a dashboard for multiple datatypes and sources or even a whole Support app like ours.

Go for it. Your seat at the Table is waiting.