In this guide, I’ll show you how to build a data-driven organizational chart based on Google Sheets data, using Apps Script and the OrgChart JavaScript library.
Recently, I used Gemini Deep Reacher to “find the most popular or useful Google Apps Script tutorials?”. There was no mention of AppsScriptPulse (rude, but if you are curious, here is the report :), but it did introduce me to the work of Joseph Petty on the GreenFlux Blog.
One tutorial, in particular, stood out for its practical application for many organisations: “Building A Data-Driven Organisational Chart In Apps Script”.
Creating diagrams, particularly organisational charts, often requires laborious design work and manual revisions. Petty’s tutorial directly addresses this common frustration by demonstrating how Google Sheets, Apps Script, and the OrgChart JavaScript library can produce dynamic, data-driven organisational charts that automatically stay current.
The Core Idea: From Spreadsheet Data to Visual Chart
The beauty of this approach lies in its simplicity and efficiency. Most organisations already maintain employee data, including supervisor information, in spreadsheets or databases. Petty’s method cleverly taps into this existing data source.
The tutorial guides you through several key steps:
Foundation Setup: Deploy a basic Apps Script web app, creating an index.html for the frontend and Code.gs for server-side logic (including a doGet() function).
OrgChart Library Integration: Introduce the open-source OrgChart JavaScript library, which uses a nested JSON structure to define the visual hierarchy.
Data Transformation: Write an Apps Script function (getEmployeeData) to pull data from Google Sheets (employee IDs, names, titles, supervisor IDs) and convert it into the required nested JSON format for the chart.
Dynamic Connection: Pass the generated JSON data from Code.gs to the OrgChart library in index.html using google.script.run.withSuccessHandler() to render the chart.
The Result? An Always Up-to-Date Org Chart
By following these steps, you can create an organisational chart that’s directly linked to your Google Sheet. When an employee changes roles, or a new team member joins, simply update the spreadsheet, and your web app will reflect these changes automatically. No more manual graphic updates!
For the complete code and detailed setup instructions, please follow the link to the original tutorial on GreenFlux Blog.
[Update 2025-05-16: A great tip from Pablo Felip Monferrer: “might be worth mentioning that the good old Sheets has a native organizational chart that can be easily published, too (hint: employee names in a third column can be displayed as tooltips)”.
Google Apps Script automates tasks (even offline) and builds web apps using spreadsheets as databases. This report presents a basic dining reservation system to illustrate key aspects of web app development with Apps Script, HTML, and Javascript.
One of my first Google Apps Script projects was an event booking system. It was a great way to learn about integrating with the various services like Google Calendar. Roll forward 14 years and it is a topic that Kanshi Tanaike has revisited with a dining reservation system built on Google Sheets with a web app frontend.
All the code is published on GitHub and even if you don’t need a reservation system a great opportunity to see how one can be coded and there could be lots of juicy little snippets that you can use in your own projects.
There’s a long standing issue with triggers installed programmatically by other triggers, that was kind of fixed last year, but users are still reporting problems, at least in certain locations (Japan, India and others). The issue in short: triggers installed programmatically would not be created, or fail systematically.
In many cases this affects add-ons (and, to be noted, the issue still persists when testing them, see Testing details); I had a slightly different scenario: a webapp that, when executed (doGet), would install a time based trigger for the user executing the script.
This article provides two solutions to an annoying issue and unlocks many possibilities for programmatically installed triggers.
In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.
There can be scenarios where you’d like to publish an Apps Script web app with ‘anyone can access’, but still provide a level of security. Here’s a nice example from Kanshi Tanaike where they use MailApp to email the user a a time limited random passcode.
Magic links and passwordless login are used by a number of services, including Slack, and rather than including a password, as demonstrated in this example, it wouldn’t take much to turn this solution into a passwordless app. There are risks associated with email based authentication and if an attacker already has access to your email so other solutions are worth considering depending on the sensitivity of your web app.
In this post I’ll provide an overview of how PayPal was integrated into AppSheet with the help of Google Apps Script. Even if you are not interested in payment integrations this post also hopefully pulls together useful tips, best practices and code patterns for reading/writing data to Google Sheets.
Hopefully this post illustrates not only just a method for integrating a PayPal payment processor into an AppSheet app, but also a method which can generally be used to extend AppSheet functionality with Google Apps Script powered Web Apps.
As a bonus you also get some of my top tips for interacting with data in Google Sheets including efficiently reading/writing data for multiple users without concurrent overwrites.
📅 A free and open source web app to make it easy for others to schedule with you –
Features
🌐 Create a unique link that others can use to book an appointment on your Google Calendar
📑 Offer mulitple types of meetings, each with their own configurable timeframe and event settings
📒 Place events on any calendar you have edit access to
📆 Confirm availability against multiple calendars
🤖 Intelligent suggestion of available free times on both your and (if accessible) the scheduling party’s calendar
🔗 URL parameters to bring the user to a specific meeting type and prefill their email1
⚡ Send a webhook push to integrate with IFTTT, Zapier, and more when an event is scheduled
🌈 Configurable accent color
🌙 Automatic light and dark mode
💳 No premium tier. 100% free.
This Apps Script solution comes thanks to a tweet from Sourabh Choraria (@choraria) highlighting a open source project from Leo Herzog which lets to deploy a highly customisable Google Calendar appointment scheduling app. The solution uses a nice JavaScript library for handling dates/times which you might find useful to include in your own projects called Luxon.
Learn how to track link clicks using Google Sheets and Apps Script to create a simple, lightweight tracking system
You live and learn! This is a regular occurrence if you are a subscriber to the work of Ben Collins (benlcollins.com). No exception with this recent post highlighting the ping attribute which can be used in <a> HTML links. This attribute has been around for a long time, but I’m sure many people like have never come across it. In the source post from Ben you can learn how to setup an Apps Script web app which will let you record link clicks in a Google Sheet.
A note of caution is whilst ping is valid HTML not all browsers choose to use it or enable it by default, something Mozilla have decided to do in Firefox. You can head over to Mozilla mdn documentation for the browser compatibility table and here is also an interesting post on how Google tracks with the ping-attribute.
When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.
A great feature of Google Apps Script is the ability to create and serve custom HTML, often used to interface data you have in Google Workspace such as Google Sheets. Google highlight a coupe of different ways you can mix Apps Script code and HTML. Some of these ways are better in terms of process time and this report from Kanshi Tanaike highlights the cost of calling Apps Script functions as scriptlets in HTML templates. The good news is you can avoid delays in your web app rendering by making asynchronous calls with google.script.run, which you can read more about in Google’s best practices documentation.
Update: I’ve replicated this benchmark (smaller dataset) with google.script.run and it was only marginally slower (0.3s) than the ‘create HTML table with Google Apps Script’:
Remove a users edit access to a Google Drive file via an Apps Script Web App.
Web App code sample
The following Google Apps Script is designed to remove a person’s edit access from a Google Drive file via a Web App. The reason for using a Web App in this instance is because removing a person’s access to a file whilst they are running Apps Script code typically results in an error (or most certainly the inability to cleanly end the code). In the project I was working on prior to this step the code needs to send some automated emails before finishing with removing the person’s access.
I use Google Apps Script to support staff and students in my job. I enjoy dabbling with creating tools to help with automation and I freely share my learning experiences on my blog, where I also have a number of useful Google Add-ons: www.pbainbridge.co.uk
Convert array values from the doGet & doPost event parameters into 2D array when writing them onto a Google Sheet.
Sourabh Choraria has hit a rich patch of Google Apps Script exploration and following the post we shared recently on Google Sheets cell precedents in this latest post Sourabh looks at restructuring data into a cartesian product. The post contains some nice other ‘Easter Eggs’, in particular, worth checking out Sourabh’s latest Workspace Add-on, Webhooks for Sheets, and a snippet of code on GitHub used in the add-on for handling GET and POST requests.
