Create a Google Sheets Tracking Dashboard with Your Workspace Data
Updated on
This article describes the method to create a visual dashboard from the data of a TimeTonic table, using Google Sheets as the calculation and visualization engine. Whenever a row is created or modified in TimeTonic, the data is automatically synchronized to Google Sheets via a webhook.
Prerequisites: be the owner or administrator of the TimeTonic book, have a Google account to create the Sheet and authorize the execution of an Apps Script.
⚠️ GDPR and data confidentiality. This method is suitable for non-sensitive operational data. Before any configuration, identify what can be sent to Google Sheets and what must stay in TimeTonic.
✅ Can be sent to Google Sheets❌ Must stay in TimeTonic
Volumes, counts, aggregatesFull names, first names, identifying initials
The system relies on three connected building blocks. Each update on the TimeTonic side is automatically reflected in Google Sheets, which calculates the statistics and generates the charts displayed in your dashboard.
1
TimeTonic sends the data
An automation triggers a webhook every time a row is created or modified in your table.
2
Google Sheets receives and processes
An Apps Script receives the data and writes it into a dedicated tab. The other tabs calculate the statistics and display the charts.
3
TimeTonic displays the dashboard
A Smart Page embeds the published dashboard, accessible to your team without leaving the workspace.
TimeTonic side: create the automation
In your TimeTonic table, go to Automate and create a new automation. Follow these four steps to configure it.
1
Choose the trigger
Select When a record view is saved to capture both the creation and the subsequent modifications of a row.
2
Leave the conditions empty
You will synchronize all rows. The script's logic prevents duplicates automatically.
3
Add the Call Webhook action
Method POST, no authentication, data structure JSON List. The properties to fill in are detailed in the next section.
4
Enter a temporary URL
Insert a temporary URL. It will be replaced by the URL generated by Apps Script during deployment.
The JSON payload expected by the script
Critical point to understand. The property names sent by TimeTonic must exactly match those expected by the Apps Script code. If you write, for example, "Intervention date" on the TimeTonic side while the script expects "date", no data will be written to your Sheet.
Here are the 9 properties expected by the reference script provided in this article. In the Call Webhook action of your TimeTonic automation, add each property with its exact name (left column) and map it to the corresponding field of your table (right column).
Property name (to paste in TimeTonic)TimeTonic field to map
intervention_numUnique identifier of the row (key used to prevent duplicates)
dateRow date (format DD/MM/YYYY or YYYY-MM-DD)
titleShort title of the item
typeCategory or type
statusCurrent status
customerCustomer reference (preferably a code, not a name)
siteLocation or geographic area
technicianIdentifier of the person responsible (preferably a login, not a name)
total_hoursDuration or numeric value to sum
💡 Tip: property names are free for you to choose (you could name them "my_date" and "my_id"), but they must always match those of the script. For the tutorial, keep these 9 names as is. If you want to adapt them to your business vocabulary, see the "Adapt the tutorial to your use case" callout below.
Google Sheets side: structure your file into three tabs
Create a Google Sheet and organize it into three distinct tabs. Each plays a specific role in the data processing chain.
Tab 1: Raw Data
Receives the rows sent by TimeTonic via the webhook. Each column corresponds to a field of your table. This tab is the source of all statistics.
Tab 2: Stats
Contains the calculation tables using SUMIF and COUNTIF formulas that read from Raw Data. This is the engine of your dashboard: everything is computed here.
Tab 3: Dashboard
Holds the charts built from the Stats tables. This is the tab you will publish and that will be displayed in your TimeTonic Smart Page.
Install the Apps Script
The Apps Script plays the role of receptionist between TimeTonic and Google Sheets. It receives the data sent by the webhook and writes it into the Raw Data tab, avoiding duplicates thanks to an update logic.
Adapt the tutorial to your use case.
If your table doesn't contain technical interventions but other data (requests, projects, tickets, cases...), you can customize. But be careful: three areas must evolve in parallel and stay consistent:
1. The property names sent by your TimeTonic automation (Call Webhook action)
2. The references to these properties in the doPost function of the Apps Script (lines payload.intervention_num, payload.date, etc.)
3. The formulas and charts of the Stats and Dashboard tabs, which must point to your own business dimensions
If the three areas are not aligned, no data will be written to the Sheet. When in doubt, keep the tutorial as is to validate the mechanism before customizing.
Not a developer? See the next section "Have an AI adapt the script to your use case": a guided method to delegate the adaptation to a conversational AI, without writing code.
1
Open Apps Script
In your Google Sheet, go to Extensions → Apps Script. A new window opens.
2
Paste the script and configure the ID
Delete the default code, paste the script provided below, then replace the SPREADSHEET_ID variable with the identifier of your Sheet (visible in the URL between /d/ and /edit).
3
Save the project
Click on the disk icon (Ctrl+S) to save. Do not deploy yet: first go through the required tests in the next section.
Have an AI adapt the script to your use case
This section is for you if: you are not a developer, you don't modify JavaScript code on a daily basis, and you need to adapt the script to your own TimeTonic table (different property names, specific business fields, custom Sheet columns).
The fastest and most reliable method is to delegate this adaptation to a conversational AI (your preferred AI). Here's how to proceed.
Goal: get a script adapted to your TimeTonic webhook configuration and to the structure of your Google Sheet, without having to write a single line of code.
1
Gather 3 elements to give to the AI
For the AI to produce a script that works for you, it needs three pieces of information: the configuration of your Call Webhook action on the TimeTonic side (the Property names and mapped fields), the structure of your Raw Data tab (column names A to N), and the reference script provided later in this article.
2
Open your preferred AI
Start a new conversation in the AI conversation tool you usually use.
3
Paste the prompt of your choice
Two prompts are provided below: a short prompt to go fast, a detailed prompt to better understand what you're asking and adjust to your needs. Choose the one that suits you and fill in the 3 blocks in brackets with your elements.
4
Retrieve the adapted script and install it
Copy the script generated by the AI, paste it into your Apps Script editor as a replacement for the previous script, save it, then move on to the next section to test it before deploying.
Short prompt (one-click copy)
Fast and direct. Copy, fill in the 3 areas in brackets, send.
Adapt this Apps Script to my TimeTonic webhook configuration so it can receive my data and write it into my Google Sheet.
MY TIMETONIC CALL WEBHOOK CONFIG (Property name → mapped field):
[PASTE YOUR WEBHOOK CONFIG HERE]
COLUMNS OF MY RAW DATA TAB (A to N):
[PASTE YOUR COLUMN HEADERS LIST HERE]
REFERENCE SCRIPT TO ADAPT:
[PASTE THE FULL SCRIPT PROVIDED IN THE TUTORIAL HERE]
Constraints to respect:
- Keep the UPSERT logic (one row per unique identifier, update if exists, create if not)
- Handle property names with spaces, accents or apostrophes via the payload["Exact name"] syntax
- Automatically recalculate year and month from the date field
- Keep both test functions testInsertNew and testUpdate with fictional data adapted to my case
- Add console.log statements in doPost to make debugging easier via the Execution log
Generate the complete script for me, ready to copy-paste into Apps Script.
Detailed prompt (for those who want to understand)
More verbose, it explains the full context to the AI and details each constraint. Useful if you want to fine-tune, or if your use case is particular.
Context: I am a no-code TimeTonic user. I want to create a Google Sheets dashboard fed by data from one of my TimeTonic tables via a webhook. An official tutorial provides me with a reference Apps Script, but this script is written for a specific use case (technical interventions) and I need to adapt it to my own use case.
WHAT I PROVIDE YOU WITH
1. My Call Webhook configuration on the TimeTonic side (each line indicates the Property name I defined and the field of my table it is mapped to):
[PASTE YOUR WEBHOOK CONFIG HERE]
2. The structure of my Raw Data tab in Google Sheets (the names of my columns from A to N, in order):
[PASTE YOUR COLUMN HEADERS LIST HERE]
3. The reference Apps Script provided by the TimeTonic tutorial:
[PASTE THE FULL SCRIPT PROVIDED IN THE TUTORIAL HERE]
WHAT I ASK YOU TO DO
- Adapt the doPost function so it reads the EXACT property names I configured in my webhook. Note: my names may contain spaces, accents, apostrophes or question marks. Use the payload["Exact name"] syntax to access them, not the payload.name syntax.
- Adapt the row construction (rowValues) so it exactly matches the order of my columns from A to N in my Raw Data tab.
- Keep the UPSERT logic (one unique row per identifier, updated if it exists, created if not). Clearly indicate which property of my webhook should serve as the unique key for this UPSERT.
- Automatically recalculate year and month from my date field, rather than depending on separate properties on the webhook side.
- Generate the concatenated keys needed for the Stats tab formulas, based on the business dimensions relevant to my case (for example status, channel, category, etc.).
- Provide two test functions testInsertNew and testUpdate with fictional data consistent with my use case (no real personal data).
- Add console.log statements at the beginning of the doPost function so I can easily debug by reading the Apps Script Execution log.
Generate the complete script for me, ready to copy-paste into my Apps Script editor. Briefly explain the points where I have a choice to make (especially the choice of the unique key for the UPSERT).
⚠️ Careful with sensitive data before sharing with an AI.
Your field names and Sheet structure can be shared without risk. However, if your screenshots or examples contain real values (beneficiary names, identifying case numbers, addresses, etc.), anonymize them before pasting. Replace real values with equivalent fictional examples (Name1, Client X, D2026-TEST-001...). This precaution applies to all personal or confidential data covered by GDPR.
Test the script before deploying
⚠️ Step not to skip. The tests built into the script let you check that everything works before deploying and configuring the webhook on the TimeTonic side. Without these tests, any issue would only surface when TimeTonic sends its first real data, with no explicit error message on the TimeTonic side.
The script includes two test functions: testInsertNew (simulates the arrival of a new row) and testUpdate (simulates the update of an existing row).
1
Select the testInsertNew function
In the Apps Script editor toolbar, next to the Run button, open the dropdown menu and choose testInsertNew.
2
Click Run
At first launch, Google asks for access authorization. Accept. Then check the Execution log (at the bottom of the editor): a message containing "status":"ok" means the test succeeded. Open your Sheet: a test row must appear in the Raw Data tab.
3
Run testUpdate
Now select testUpdate in the dropdown menu, then Run. The previous test row must be updated (not duplicated). This is the proof that the uniqueness logic works.
4
Deploy as a web app
Tests validated. Click on Deploy → New deployment → Web app. Execute as: Me. Who has access: Anyone. Accept Google's authorization.
Google then generates a URL ending in /exec. Copy it and paste it into the URL field of the Call Webhook action in your TimeTonic automation, replacing the temporary URL entered in step 4 of the TimeTonic section.
💡 About triggers: no Apps Script trigger needs to be manually configured. The doPost function of the script runs automatically each time a call hits the /exec URL of the deployment. TimeTonic triggers everything via the webhook. The dropdown menu in the toolbar is only used for manual tests.
Build the calculation tables in the Stats tab
In the Stats tab, create calculation zones using two simple functions that read data from Raw Data: SUMIF and COUNTIF.
SUMIF adds up a value filtered by a criterion:
=SUMIF(criteria_range ; criterion ; sum_range)
COUNTIF counts the number of occurrences of a value in a column:
=COUNTIF(criteria_range ; criterion)
💡 Tip: rather than writing the value directly into each formula, list your criteria in a column and reference the cell. You write the formula only once, then double-click on the blue square at the bottom right to duplicate it automatically over all rows.
Build the charts in the Dashboard tab
For each calculation table created in the Stats tab, generate a chart by following these four steps.
1
Select the table
In the Stats tab, select the range of cells corresponding to your calculation table.
2
Insert the chart
Menu Insert → Chart. Google Sheets automatically suggests a suitable type that you can modify: bars, lines, donut or pie.
3
Customize the appearance
Edit the title, colors, legend and chart type via the customization panel on the right.
4
Move to the Dashboard tab
Three dots on the chart → Copy chart. Paste it into the Dashboard tab and arrange it as you wish.
Publish the dashboard securely
⚠️ Before any publication, check your data. Refer to the OK / Not OK table at the beginning of the article. Only non-sensitive operational data may be exposed via a public URL.
Three precautions to take in order to protect your source data and expose only the dashboard.
1
Hide the sensitive tabs
Right-click on the Raw Data tab → Hide sheet. Do the same for the Stats tab. Only the Dashboard tab remains accessible.
2
Publish only the Dashboard tab File → Share → Publish to web. Select only the Dashboard tab (not the entire document). Format: Web page. Publish and copy the generated URL.
3
Lock sharing in strict read-only mode
Share button → General access: Anyone with the link, role Viewer. In the advanced settings, uncheck the options for downloading, copying and printing to prevent any exfiltration.
Embed the dashboard in a TimeTonic Smart Page
Final step: display your dashboard directly inside TimeTonic via the Smart Page module.
1
Add a Smart Page
In your TimeTonic workspace, click on Add then select the Smart Page module.
2
Paste the URL of the published dashboard
Insert the URL generated in the previous step and name your Smart Page.
3
View the dashboard in real time
The dashboard is displayed read-only in your workspace, updated each time a row is modified in the TimeTonic table.
If something doesn't work, check in this order
Here are the 5 most common issues and how to resolve them. Follow the checks in the order suggested.
1. My Sheet stays empty despite the "success" status on TimeTonic
The HTTP 200 status displayed by TimeTonic only means that Google responded, not that the script managed to write into the Sheet. To get the real picture, open your Sheet, go to Extensions → Apps Script, then click on the clock icon "Executions" in the left menu. There you will see the real logs on Google's side, with any errors.
2. The Apps Script log shows "Missing intervention_num in payload"
The property names sent by TimeTonic do not match those expected by the script. Check your Call Webhook action on the TimeTonic side and compare the property names with the table in the "JSON payload expected by the script" section. The names must be identical to the character.
3. My changes to the script are not taken into account
After every change to the code, you must create a new version of the deployment. Go to Deploy → Manage deployments, click on the pencil icon of your deployment, select New version, then Deploy. The URL does not change, but the executed code is updated.
4. The script returns "Sheet 'Raw Data' not found"
The tab expected by the script doesn't exist in your Google Sheet, or its name is different. Make sure the tab is named Raw Data (two words, space in the middle, no accent). Also check that the SPREADSHEET_ID variable at the top of the script matches the identifier of your Sheet.
5. Numbers appear as text in the Sheet
Numeric format issue (decimal comma vs dot). The script normally handles this case, but if a cell remains in text mode, select the affected column, then Format → Number → Number. Never use "Split text to columns", which would destroy the adjacent columns.