Combination Charts

EBazan

Hello,

Can we create combo charts (bar/line) in Smartsheet?

Ty

jmyzk_cloudsmart_jp

Hi @EBazan

Unfortunately, Smartsheet does not currently support the combo chart.

One way to create a comb chart in Smartsheet involves syncing your data to Google Sheets, leveraging its charting capabilities, and then embedding the finished chart back into a Smartsheet dashboard. Here's a guide on how to do this:

1. Smartsheet API Setup

• Get API Access: Generate an API token in Smartsheet for authentication.
• Identify Data: Select the data or sheets in Smartsheet you want to sync.

2. Google Apps Script Setup

• Create a Script: Go to Extensions > Apps Script to open the script editor in Google Sheets.
• Write Script to Fetch Data: Develop a script that uses the Smartsheet API to fetch the data you need and load it into Google Sheets.

3. Create and Customize a Comb Chart in Google Sheets

• Select Data: Highlight the data you want to create the chart.
• Insert Chart: Go to Insert > Chart, select ''combo chart,'' and customize the settings to fit your data presentation.

4. Publish and Embed the Chart in a Smartsheet Dashboard

• Publish the Chart: From Google Sheets, click on the chart, select "publish chart," choose the embed option, and copy the HTML code provided.
• Embed in Smartsheet: Add a 'Web Content' widget to your Smartsheet dashboard and paste the HTML embed code.

This approach utilizes Google Sheets's graphic strengths for better visualization and effectively integrates these visuals within your Smartsheet environment.

In the demo dashboard below, you can change the language of the Dropdown list and click the Google Apps Script link to trigger the update of the Google sheet's dropdown list data.

https://app.smartsheet.com/b/publish?EQBCT=fac30a0b756a46e7a6e4ee9dc89530ff (Link to the demo dashboard)

KPH

Not yet, but you can add your comments to this product enhancement request (or any others)

https://community.smartsheet.com/discussion/98697/dashboard-charts-secondary-axis-additional-data-points-target-line

jmyzk_cloudsmart_jp

Hi @EBazan

Unfortunately, Smartsheet does not currently support the combo chart.

One way to create a comb chart in Smartsheet involves syncing your data to Google Sheets, leveraging its charting capabilities, and then embedding the finished chart back into a Smartsheet dashboard. Here's a guide on how to do this:

1. Smartsheet API Setup

• Get API Access: Generate an API token in Smartsheet for authentication.
• Identify Data: Select the data or sheets in Smartsheet you want to sync.

2. Google Apps Script Setup

• Create a Script: Go to Extensions > Apps Script to open the script editor in Google Sheets.
• Write Script to Fetch Data: Develop a script that uses the Smartsheet API to fetch the data you need and load it into Google Sheets.

3. Create and Customize a Comb Chart in Google Sheets

• Select Data: Highlight the data you want to create the chart.
• Insert Chart: Go to Insert > Chart, select ''combo chart,'' and customize the settings to fit your data presentation.

4. Publish and Embed the Chart in a Smartsheet Dashboard

• Publish the Chart: From Google Sheets, click on the chart, select "publish chart," choose the embed option, and copy the HTML code provided.
• Embed in Smartsheet: Add a 'Web Content' widget to your Smartsheet dashboard and paste the HTML embed code.

This approach utilizes Google Sheets's graphic strengths for better visualization and effectively integrates these visuals within your Smartsheet environment.

In the demo dashboard below, you can change the language of the Dropdown list and click the Google Apps Script link to trigger the update of the Google sheet's dropdown list data.

https://app.smartsheet.com/b/publish?EQBCT=fac30a0b756a46e7a6e4ee9dc89530ff (Link to the demo dashboard)

EBazan

Very cool, thanks for the tip.

Is there similiar functionality available for Excel?

EBazan

Can you provide guidance on this step? I have not created scripts before.

Thank you!!!

2. Google Apps Script Setup

jmyzk_cloudsmart_jp

Hi @EBazan

If you can access Power BI in your Office 365, you can import Smartsheet and create a chart. (Though you can import Smartsheet data into Excel using Power Automate and subsequently create a combo chart within Excel, using Power BI for this task is more straightforward and efficient.)

https://app.smartsheet.com/b/publish?EQBCT=437fcc864c8e4054b273a04d7222a0a6

Here's a detailed guide on how to pull data from Smartsheet into Power BI and then create a combo chart:

Step 1: Connect Power BI to Smartsheet

1. Open Power BI Desktop.
2. Get Data: In the Home tab, click "Get Data".
3. Choose Online Services: Select "Online Services" from the categories listed.
4. Select Smartsheet: Find and select "Smartsheet" from the list of services. (Both Smartsheet Legacy & Beta works)
5. Connect: Click on "Connect" to start the connection process.
6. Sign In: You will need to sign in to your Smartsheet account. Enter your credentials and authorize Power BI to access your Smartsheet data.
7. Select Sheets: After a successful login, a list of your Smartsheet sheets will appear. Select the sheet(s) you want to import into Power BI.
8. Load or Transform Data: Click "Load" to bring the data directly into Power BI, or "Transform Data" if you need to adjust the data in the Power Query Editor first (e.g., filter columns, change data types).

Get Data > Online Services > Smartsheet

Navigator > Load Smartsheet Data (I transformed the data more like an Excel Sheet)

Step 2: Create a Combo Chart

1. Choose Visualization: In Power BI Desktop, in the "Visualizations" pane, you might not find a direct combo chart option. For a combo effect, you'll use the Line and Stacked Column Chart.
2. Add Data Fields:
   • Drag and Drop the Fields: Drag the fields for the column and line values to the respective areas in the visualization pane.
     • Drag a field to the "Shared axis" area. This will be the common axis, typically the x-axis.
     • Drag the field for the column values to the "Column values" area.
     • Drag the field for the line values to the "Line values" area.
3. Adjust the Chart: Click on the chart to select it, then use the "Format" pane to adjust the appearance of your chart:
   • Y-Axis Settings: You can configure the primary and secondary Y-axis (e.g., scale, and title) if necessary.
   • Data Colors: Customize the colors of your columns and line to improve readability or match your company's branding.
   • Labels and Titles: Add data labels and titles for clarity.

I changed the data format from text to decimal and the summary method to average. (Sum will also work. If left as text, BI gives you only count the summarize method, which displays only 1s.)

Step 3: Refine and Publish

• Refine Your Chart: Make any additional tweaks to your chart or report layout to ensure it communicates the data.
• Save Your Report: Save the Power BI report file (.pbix).
• Publish: When ready, publish your report to the Power BI Service for sharing with others or for embedding in the Smartsheet Dashboard. (As shown in the top image.)

EBazan

Thank you so much for the direction!!!

jmyzk_cloudsmart_jp

@EBazan

Glad to help! 😁
If you found this information helpful, consider clicking Awesome to help others discover it, too.