How To Use Google Gemini To Create a Google Sheets Dashboard

Building a Dashboard with Google Gemini 2.5 and Google Sheets

Introduction to the Tutorial

• This video tutorial focuses on creating a dashboard using Google Gemini 2.5 and Google Sheets Apps Script, requiring no prior coding or Google Sheets knowledge.

• The process will be explained step-by-step, making it accessible for beginners.

Overview of the Dataset

• The dataset used contains sales-related information over two years, including customer names, product details, sales locations (e.g., Arizona, New Jersey), and financial metrics like sales amounts and profits.

• Prompts for analysis will be provided in the video description to guide users through the process.

Analyzing Data with Google Gemini

• Users are instructed to analyze data using Google Gemini to determine what information should be displayed on the dashboard (numbers, graphs, charts).

• Accessing Google AI Studio allows users to try out the newly released Gemini 2.5 pro version for their project.

Creating Initial Dashboard Layout

• After executing initial prompts in Google Gemini, suggestions are provided for important numbers and types of charts/graphs to include in the dashboard layout.

• A draft layout is prepared: key metrics at the top and charts/graphs below to illustrate trends and insights effectively.

Implementing Code from Google Gemini

• Users copy prompt number two from the prompts page into Google Gemini to generate necessary code for their dashboard setup.

• Instructions detail creating a file named Code.gs within an Apps Script project where backend code is pasted after copying it from Gemini's output.

Frontend Development Steps

• An HTML file named index.html must also be created within the Apps Script project; relevant HTML code is copied from Google's suggestions into this file.

• Deployment steps involve saving changes and setting up a web app deployment type before accessing the newly created dashboard via a generated link.

Customizing Your Dashboard

Method 1: Editing Code Directly

• Users can customize their dashboards by editing code directly in Apps Script if they possess basic coding skills; adjustments can include changing background colors or chart widths as needed.

• Specific CSS modifications are demonstrated by removing background colors and adjusting chart dimensions for better alignment of visual elements on the dashboard.

Method 2: Using Chart Libraries

• For further customization options such as adding markers to charts, users are directed to consult documentation from libraries like Apex Charts that were utilized during development.( t = 535 s)

How to Customize Charts in Google Apps Script

Adding Markers to Sales Trend Chart

• The speaker demonstrates how to add markers to a sales trend chart by copying sample code from the Apex Charts website and pasting it into the existing code in the Apps Script project.

• After making necessary adjustments, the project is saved and the dashboard page is refreshed to reflect changes.

Using ChatGPT for Code Adjustments

• The speaker introduces a method using Google Gemini or ChatGPT for users with limited coding knowledge, particularly for adjusting X-axis date labels on charts.

• The process involves searching for the sales trend chart code, copying it, and asking ChatGPT to modify the format of X-axis labels.

Changing Chart Types with AI Assistance

• The speaker explains how ChatGPT can be used not only for minor adjustments but also for significant changes like converting a half donut chart into a full donut chart.

• This involves copying the relevant code from Apps Script, prompting ChatGPT with it, and then implementing the revised code back into Apps Script.

Adding Data Labels to Charts

• Instructions are provided on adding data labels to specific charts such as "Top 10 Customers" and "Profit by Category" by changing settings in their respective codes.

• The final dashboard reflects these customizations, showcasing added data labels which enhance clarity.

Final Thoughts and Resources

• The speaker concludes by mentioning additional changes made to finalize the dashboard design.

• They offer resources including prompts, links to Google Sheets, and complete code in the video description for viewers' use in their projects.