Grab this automated ConvertKit Report in Google Sheets to monitor your email list growth and use it to project your future list growth.
Ben Collins shares the solution he uses to produce a daily ‘ConvertKit’ PDF report. Even if you don’t use ConvertKit this post is still worth a read as the basic model of getting data from a third party API into Google Sheets and sending a PDF summary could come in handy. Perhaps not surprising given this post comes from Ben it includes some clever Google Sheet functions to prepare and shape data.
One of the benefits of joining the Google Developers Experts (GDE) program was the opportunity to meet and speak to Google Analytics GDEs. As well as an insight into how Google Analytics and related products can be used, it has also been an opportunity to collaborate with experts including Nico Miceli and Simo Ahava to explore how Google Apps Script and Google’s digital marketing products could be used together. I count myself as very fortunate to have been able to collaborate with these experts, particularly the benefits of sitting down and discussing the opportunities afforded by Google Apps Script.
For those in Digital Marketing and Analytics without a Nico or Simo on hand where do you turn? Michele Pisani, for one, has been and continues to be a prolific contributor to the Digital Analytics and Google Apps Script community. Unless you are fluent in Italian you may not have come across Pisani’s work before on AppsScript.it, his associated YouTube channel and curation of “Fatti di Apps Script”, an Italian Facebook group for Apps Script users.
The book is aimed at all those who work or enjoy in the field of data analysis, SEO, ADV, Social Media and in general who are looking for a productive advantage to support their activities, with significant savings in time and at zero cost.
I refer to configurations, management and governance in one click for Google Analytics and Google Tag Manager, support for Ecommerce, access control and data according to the GDPR law, connectors with third-party services, add-ons, Web Apps, tracking, automated reporting and simplified data visualization.
All with a few lines of JavaScript, using only Google Apps Script, without the need for a domain, hosting and knowledge to manage them. The tool offers a simple environment accessible from the Web to overcome the technical and psychological gap due to the need to use complicated external paid tools.
As you can imagine the book includes information on tasks like report automation, however, even with this Pisani often goes beyond ‘basic’ solutions to explore creative and clever ways Apps Script can be used to enhance Digital Marketing activities. Examples of this include consolidating all payments and refunds in Google Analytics, monitoring version changes to Google Tag Manager, and opportunities with adding some Machine Learning to Google Sheets.
Throughout Pisani’s book there are also clear explanations of not just how you can automate processes, but the clear benefits of why you should be doing it in the first place. This included best practices to ensure data integrity as well as legal and service requirements around data protection.
In the field of Digital Analytics I would classify myself as an amateur enthusiast rather than a seasoned professional, but from what I’ve seen in Pisani’s book I can imagine it will be a very useful addition for anyone with a little coding experience and desire to learn more about digital marketing automation.
[Michele kindly provided a free copy of “Digital Analytics Automation with Google Apps Script” for review.]
This piece of work was both challenging and a lot of fun to produce, and it’s something I’ve wanted to get round to for a long time. Apps Script libraries are a great way to reuse work you and others have done, but you have to be careful that they don’t get out of date. Libraries that refer to other libraries are complex to keep up to date, and worst of all they may disappear or their permissions change at any time.
Google recommend you don’t use them in Add-ons, and although they focus on ‘load efficiency’ (I did a study on this a few years back and found absolutely no evidence of a measurable load penalty for libraries: see Measuring library load speed), the above are probably better reasons to bundle all the code you use in your app or Add-on.
As noted by Bruce there may be a number of reasons you might want to inline libraries as part of your Apps Script projects, particularly if you are reusing third-party libraries where there is a risk the code could disappear.
In this video you will see how you can use Google Apps Script to export a Google Sheets document as a PDF
It’s been a while since we featured a video tutorial from Chanel Greco, but that doesn’t mean she hasn’t been busy. In this recent tutorial from Chanel you can learn how to use Google Apps Script to export a Google Sheet as a PDF Document. You can watch this and other videos from Chanel on the saperis YouTube channel.
In this post, we’re going to look at how we can work with the responses a form user submits.
We’ll look at two main ways, 1) Getting the form responses from a Google Sheet, 2) Getting the form responses directly from a Google Form.
To show some practical uses of this, we’ll do the following:
Set up a simple problem-reporting log, which will email the relevant parties the problem in a classroom. We’ll do this via the Sheet and via the Form.
Get students’ pieces of writing submitted via a Google Form and copy them to their individual sheets, ready to have feedback added.
Set up a simple appointment system, which will update itself as people take the appointments, leaving only the available ones on the Form.
This post taken from Barrie Roberts’ latest book “Beginner’s Guide to Google Apps Script 2 – Forms“, available on Amazon here. The post covers some different ways that you can interact with Google Form responses and contains lots of useful code and tips that might be useful to other Google Apps Script developers.
I have created a handy highlighting tool using Google Sheets and Google Apps Script. The specification of this tool is very simple.
The user pastes a message into the certain cell (A2).
The tool checks if the message contains some words which listed in another dedicated sheet(Spam Word List).
The tool highlights (bold and color red) the found words in the message.
Here is a nice post demonstrating how you can use Google Apps Script to highlight keywords within a Google Sheets cell. The solution uses SpreadsheetApp.newTextStyle() to create a custom style which is then applied to part of the cell text.
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.
This article covers how to pull scripts from multiple projects and import them into another project. You can even use this to make container bound script standalone, or visa versa. … This article will cover the library that does all that, along with various other usage examples – for example, pulling in code snippets from or libraries, merging manifests, or testing add-ons.
Some more magic from Bruce Mcpherson this time creating and documenting a library that can be used to copy/replace scripts. Bruce’s illustrates this with an example of pushing a standalone script to a container bound Google Sheet project and I’m sure you can find many other ways this could be useful to maintain script projects.
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.
