AppsScriptPulse

Marketing Data Extraction using SOAP API — Apps Script Automation

Recently I had setup a daily extraction automation from a marketing platform and they had SOAP API. I had worked with xml before, so after some trial and error I was able to fetch marketing data from trafficvance.com platform right inside Google Sheets

Nice post from Urwa Shabir highlighting how to interact with 3rd party APIs using SOAP which relies heavily on XML. The post covers formatting a SOAP request now that SOAPService is deprecated in Apps Script, as well as parsing the XML response.

Source: Marketing Data Extraction using SOAP API — Apps Script Automation

DNS in Google Sheets – Cloudflare Resolver

They thought we wouldn’t go there, but they thought wrong. If you want or need to find out some DNS records inside of Google Sheets, create a Google Function

Eric Koleda spotted (see tweet) that Cloudflare has been having some fun with Google Apps Script sharing a custom function that lets you do a DNS lookup for domain names in Google Sheets. Eric notes he can’t imagine when you would need to do this, but I always find it encouraging to see big companies using and promoting Google Apps Script.

Source: DNS in Google Sheets – Cloudflare Resolver

Hacking it: Generate PDFs from Google Forms

Some time ago, I had a request from one of my colleagues to help automate a rather repetitive and might I add, highly prone to error, process. One of the clients required her to fill in the same form several times in a day, manually, and then send it back for approval and signing. Of course, I’m simplifying a bit but that was what it was in essence.

In this detailed post, Neha demonstrates how to use Google Forms to generate professional looking PDFs from the data submitted via the form, using Google Sheets and Apps Script.

Source: Hacking it: Generate PDFs from Google Forms

Delegating Client-Side Requests with google.script.run in Google Apps Script – Jeff Everhart

After a while, writing code becomes less about knowing all of the specifics of a language, framework, or platform, but more about being able to match patterns. After seeing a sufficient number of different problems, you get better at saying, “Hey, this bit of problem X seems a lot like this part of problem Y, maybe we could use that.”

In this post, learn about the Delegation Pattern that describes an object whose role it is to accept requests, delegate those responses to other objects, then return the responses to the original caller.

Source: Delegating Client-Side Requests with google.script.run in Google Apps Script – Jeff Everhart

How to make a Ladder Card game with Apps Script

In our foreign language classes, one of the classic activities we do to help students learn new language, is to print phrases onto card, then cut the cards up. We then write the answers on the back of the cards. They then place the cards on a desk and then go through the cards one by one, trying to remember the correct answer.

Here we’re going to make a simple ladder card game using Apps Script and Google Sheets.

Source: How to make a Ladder Card game with Apps Script

Streamline access to resources for vendors via Google Sheet – Sheets to Apps

Need to share a variety of documents with vendors? Don’t want to share access one by one? In this episode of Sheets to Apps, we’ll cover how to use an Apps Script in a Google Sheet to automatically add members to a Google Group, helping you share documents at a large scale that will save you time.

We’ve previously shared the Medium post that provides more details for setting up this solution. If you missed that post or were unclear Alexandrina Garcia-Verdin, better known in the community as AGV, provides a very useful overview.

The code and other resources presented in this episode are available from the video description.

How to format cells through code in Google Sheets

If you had used the GUI to manage your custom formulas used for conditional formatting, then to see and manage that logic, you would have to find and select the correct cell, or select the entire spreadsheet, and then right click and select “Conditional Formatting”, or go to the menu and select “Format -> Conditional Formatting”, to actually see and edit that logic. Which can be quite inconvenient at times.

Set conditional formatting rules through your script, which would allow you to keep everything configured in a single script.

Source: How to format cells through code in Google Sheets

Integrate Google sign-in using Firebase authentication on your Google Apps Script web app

make use of firebase’s pre-built ui to authenticate users on our google apps script web app

Sometimes with Google Apps Script web apps you need a way to authenticate the user whilst also publishing the web app to run under a specific account. Sourabh Choraria details how Firebase can be setup allowing you to authenticate users with their Google account.

Source: integrate google sign-in using firebase auth on your google apps script web app

Grant access to resources 🚣 from a Google Sheet in G Suite

Use a Google Group to work cross-functionally with vendors, partners, customers, and volunteers outside of your domain, and scale access to work assignments and leverage security controls.

Share documents, events, and communications with users outside (or inside) your G Suite domain.

Source: Grant access to resources 🚣 from a Google Sheet in G Suite

YouTube Uploader – Let Others Upload Videos to your YouTube Channel – Digital Inspiration

Make your own YouTube uploader form and let anyone upload videos of any size to your YouTube channel. The uploader can send videos even anonymously without a Google account.

It is easy to upload videos to your own YouTube channel but there’s one limitation. Only the owner of the channel can upload videos to their YouTube account and they cannot delegate the task to someone else in the team.

Google Drive has the concept of shared folders. A folder can have multiple collaborators and anyone can upload files to that folder. Wouldn’t it be useful if such a feature were available for YouTube channels as well? You maintain a single YouTube account and multiple people can upload videos to your common account without the owner having to share their Google account credentials with anyone.

Source: YouTube Uploader – Let Others Upload Videos to your YouTube Channel – Digital Inspiration

Capture Gmail Messages in a Google Sheet using Google Apps Script | Practical Ecommerce

With Google Apps Script, marketers can capture Gmail messages, parse them for relevant information, and add them row-by-row to a Google spreadsheet. This can be especially helpful for capturing leads or contacts.

A comprehensive write-up from Armando Roggio on using Google Apps Script to copy emails from your Gmail account to Google Sheets. As an added bonus the tutorial is also written using the new V8 runtime syntax.

Source: Capture Gmail Messages in a Google Sheet | Practical Ecommerce

Google Slides API — a deep dive on its powerful image manipulation methods

A post dedicated to devs and highlighting the power of the Slides API.

Use Google Apps Script and the Google Slides API to create a card game like Dobble.

Source: Google Slides API — a deep dive on its powerful image manipulation methods

A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8 – MASHe

Last year I had a ‘mail merge using Gmail and Google Sheets’ contribution accepted on the G Suite Developers Solution Gallery. Just over 6 months on there has been lots of useful feedback and requests for solutions to some of the limitations of the original published script. In the meantime Google has also made the new V8 runtime for Google Apps Script generally available. Given this it seemed appropriate to revisit the original solution updating it for V8 as well as covering the most commonly asked for changes. In this post I’ll highlight some of the coding changes as well as some easy modifications.

This post picks up some common requests I get for features like advanced send parameters (cc, bcc, sender name/from, replyTo), sending emails with emoji/unicode and scheduling/triggering bulk emails. Some other areas covered in this post might be of general interest to Apps Script developers interested in using formatted Google Sheets cell values for currencies, dates and more as well as detecting/ignoring filtered hidden rows.

Source: A bulk email/mail merge with Gmail and Google Sheets solution evolution using V8 – MASHe

How To Connect Google Sheets To A Database, Using Apps Script

App Script comes with a JDBC Service that lets you connect to MySQL, Microsoft SQL Server, and Oracle databases.

This is a guest post from Mike Ritchie (on benlcollins.com), co-founder of Seekwell.io, which adds SQL to the apps you need it in.

Source: How To Connect Google Sheets To A Database, Using Apps Script

Automating Delivery Service for a Supermarket linked with Tuk-Drivers from a Finance Company with Minimum Effort

With Google App Scripts, SMS Gateway and CRM APIs we managed to come up with a solution that made a rewards awarding mechanism for the riders. Once a Rider completes a Delivery they are getting rewards and rewards are mapped through the Leads in CRM solution.

While no Apps Script code is included, it’s a useful usecase of how Apps Script is helping businesses deliver.

Source: Automating Delivery Service for a Supermarket linked with Tuk-Drivers from a Finance Company with Minimum Effort

How to Colorize Google Calendar Event Using Apps Script

Google Calendar provides a facility to create multiple calendars in order to keep different kind of events tidy. It also let’s us colorize them, but in the addition to that, you can also colorize different events in a single calendar!

Automatically colorize your calendar events using the power of Google Calendar and Google Apps Script platform.

Source: How to Colorize Google Calendar Event Using Apps Script (1/2)

Google Apps Script: Get Google Doc Text Based on Reference Characters – Yagisanatode

This simple tutorial should get you started with grabbing text from a Google Doc for other uses. You could certainly make a lot of changes to the code to introduce more search rules. Likewise, you could use regular expressions to search for some really cool stuff, but for a simple overview this should get you started.

This post provides a detailed how-to of getting a list of information from the document that you have noticed are between two sets of characters.

Source: Google Apps Script: Get Google Doc Text Based on Reference Characters – Yagisanatode

Using Google Apps Script to capture who sends email from your delegated gmail account

Everything a Google Apps Script Developer wanted to know about reading hyperlinks in Google Sheets … but was afraid to ask

The problem: hyperlinks in Google Sheets can be in three different formats so if you have a script or add-on that relies on handling links in cells it can cause some headaches.

This article covers a thorough walkthrough of using Advanced Sheets Service to read hyperlinks from cells with different link formats.

Source: Everything a Google Apps Script Developer wanted to know about reading hyperlinks in Google Sheets … but was afraid to ask

Using Google sheets to build a very simplistic Inventory Management solution for your small business.

Google sheets has Apps Script which extends its functionalities — allowing the automation of tasks, creation of UIs, webapps, add-ons, etc. This makes it a very powerful tool to create customized solutions for your needs.

A simple approach on dealing with complexities of managing inventory for small businesses using Spreadsheets and Google Apps Script.

Source: Using Google sheets to build a very simplistic Inventory Management solution for your small…

Subscribe to Apps Script Pulse...