Scripting in Google Sheets gives you super power, and it’s very easy to use APIs to enrich your document.
In this article I’ll show you how-to:
Create custom functions in Google Sheets
Use Clearbit API to translate a company name into a company domain
Use Apollo API to get details about a company using a company domain
Bonus: Use Google CacheService to avoid API rate limits
We recently highlighted a Medium post on How to get Linkedin profiles in Google Sheets. Here is a similar example which combines data from the Clearbit and Apollo to return company information including other social media urls and more.
Image credit: Alexis Laporte
Some great tips in this post including using the Cache Service, find out more via the source link 👇.
Apps script code to manage CSV files input and output for hash code 2022 of Google.
This post from Stéphane Giron provides some very useful Google Apps Script snippets for working with .CSV data files. Included in the post are ways to:
Retrieve CSV files with Apps Script and read data;
Treating CSV files; and
Create output CSV files
As a bonus the post shows you how you can create a backup text file of your apps script file.
Some time ago, I made a custom Google Sheets formula to automatically get the Linkedin profile of a person/company. It can be helpful to anyone using Google Sheets as a CRM or recruitment, product feedback, or marketing tool. It saved me a lot of boring copy/pasting, so hopefully, it can save some time for you as well
Interesting project from Giacomo Melzi that uses the Google Custom Search API to search LinkedIn for personal and company profile pages. As noted by Giacomo:
Since Linkedin profiles are behind a login (you have to be logged in to check someone’s profile), the formula relies on a workaround. The script performs a domain-constrained search via the Google Custom Search API.
That’s something anyone can do manually in a standard Google search. Just try adding site:{domain} before your search query, and your results will be filtered by domain.
This technique can be used on other sites that are behind a login but indexed by Google. A couple of important limitations to be aware of, in particular, free quota limits. Check out the source post for more details and the code to run this yourself.
This is the first of a three-part post, where we’re going to look at how you can create an appointment system using Google Forms and Sheets and with the use of Apps Script, how you it will update the available times on the forms and how it will send automate confirmation emails to those making the appointments. I’ve used this system for parents evening meetings, but it could be adapted for any area that need appointments.
This series of posts from Baz Roberts details an appointment booking system created in Google Sheets and Forms. There is a lot of details, tricks and tips across the post series so well worth spending the time to unpick what has been shared.
Google Apps Scripts is incredibly powerful and enables complex systems to be built on top of Google Apps. It can be a great choice when you need to quickly prototype an idea or design a solution that’s customizable by non-technical users.
In this article, I will walk through a simple example of building a “Slot Booking System” using Google Sheets, Google Calendar, HTML, Tailwind CSS and Google Apps Script.
Nice little Apps Script project shared in this post using Google Calendar and a published web app, making it possibly a nice starter project for something bigger.
Take control of your inbox by unsubscribing from emails you never have time to read, or have lost interest in. … I opted to use Google Sheets as a centralized place to store emails and their unsubscribe links to make it as user-friendly as possible. This also provided the added benefit of working with Google Apps Script to extract email metadata from Gmail to Google Sheets.
Interesting little Apps Script project for those interested in tidying up your inbox and also an opportunity to try a bit of python. Alternatively you might want to have a go at modifying this solution to also extract unsubscribe links with Google Apps Script
Google Apps Script has some amazing built-in stuff. It gives us native access to all Google apps like Sheets and Gmail, seamlessly integrates with GCP services like BigQuery, allows for the building of interfaces with HTML and CardService, facilitates the creation of simple webhooks/APIs and web apps with simple and efficient client-server communication, can make use of any API through UrlFetchApp, and can be bundled into add-ons for efficient distribution. In my experience, it’s enough for 99% of all Google Apps Script developers.
However, one thing that Google Apps Script is missing is modules. NPM has an extremely impressive database of JavaScript modules that don’t automatically integrate with Apps Script. Of course, in Apps Script we have libraries, but the selection is extremely limited and there is no marketplace for those. By the way, who wants to participate in creating one? Let me know in the comments! However, the very first warning on the libraries documentation page notes that libraries make Apps Script slow. Well, Apps Script is already far from being the fastest programming language on Earth, so slowing it down further is not an idea that I’m a fan of!
But what if I told you that there actually is a way to use some NPM modules in Apps Script? You just need to bundle them with Webpack. Not sure what I mean? Keep reading.
As this amazing year comes to an end, I decided to note down all my blogs, videos in the past year. This has been an amazing year for me academically and professionally. I have passed all my engineering exams and am in my 3rd year of engineering. I am also constantly helping my college automate internal processes using Google Apps Script and Google Workspace. You can check them out in the blog link given below
Looking at his summary post from Aryan Irani we’ve clearly not been able to keep up here on Pulse with the volume of content that they have published. The summary post linked here is an opportunity to review some of Aryan’s top community contributions from this year.
They say you only live once, and, with that, you may only once find yourself at a company with a video-game-centric culture. Life at Twitch creates a unique opportunity to bring the magic of gaming to your workday, even when you’re in esoteric corners of the work world such as People Analytics.
When it came out, the new Google Apps Script IDE was, of course, a big deal. It’s way better than the legacy one. It brought syntax highlighting, keyboard shortcuts, command palette and just a much better UI experience.
In fact the reason it’s so good is that it’s built on top of VS Code Monaco editor. But it’s definitely still far away from what a real VS Code installation can do. You can get all the functionality of the online IDE and much more more: autocomplete, custom themes, installation of modules, linting, snippets, etc.
