AppsScriptPulse

Solutions, Sheets, Beginner

Adding STAT Reports [extracting .csv email attachments] to Google Sheets Using App Scripts

Tags:
| | 0 Comments |

Image credit: moz.com

The team at MacMillan Search has generated a lot of value by combining automated STAT reports with Google Sheets through the script they share in this post. From adding ranking details to other tool’s outputs to giving the content teams up-to-date “People Also Ask” reports, the end result has proven to be a great time-saver in our week-to-week SEO workflows by reducing manual work and providing standard outputs that easily integrate with any spreadsheet.

I always enjoy dipping into content from the SEO community as it has some really creative and innovative solutions. This example from MacMillian Search is a use case I’m familiar with, getting data out of a system as an email report with .CSV attachment and using Google Apps Script to do all the automation to update a Google Sheet. If you are not in SEO still worth a read as the pattern used here may come in handy.

Source: Adding STAT Reports to Google Sheets Using App Scripts

Snippet, Beginner, Sheets

How to pass Apps Script data directly into a HTML page

Tags:
| | 0 Comments |

The following Google Apps Script code is designed to create a HTML popup in a Google Sheet and pass some data directly into it so that the popup can display the information.

Display Apps Script code directly in a HTML popup

This was part of a larger project I was working on so I have stripped all of that away here and kept the bare minimum for demo purposes. I already knew how to get the HTML page to run a Function once it had loaded to then access static data from within the Google Sheet, but what was tripping me up here was if that data was coming directly from Apps Script itself – such as an error message from a try/catch.

Source: The Gift of Script: How to pass Apps Script data directly into a HTML page

Snippet, Beginner, Drive

Change file permissions from Editor to Viewer

Tags:
| | 0 Comments |

Google Drive Access Screen

How to change Google Drive file permissions from Editor to Viewer as said user.

The following Google Apps Script is designed to change the Google Drive file permissions of a specific user from Editor to Viewer. The function actually came about when needing to end a process where a user had been editing a Google Sheet on a Shared drive that they should only then have Viewer access to. To complicate matters the Apps Script code is running as said user when it needs to reduce their own permissions.

Source: The Gift of Script: Change file permissions from Editor to Viewer

Solutions, Forms, Beginner

Using Google Forms API with Google Apps Script

Tags:
| | 0 Comments |

The Google Forms API is currently in Open Beta. For Google Apps Script users the new Forms API is worth keeping an eye on as it enables functionality not currently included in the native FormsApp service. In this post from Kanshi Tanaike an example is looked at where the Forms API is used to insert an image to a question. If you are interested in trying this example out remember that you currently need to apply for access at the Early Adopter Program page.

Source: Using Google Forms API with Google Apps Script

Snippet, Solutions, Sheets, Beginner

Create a new Sheet tab and format a header row

Tags:
| | 0 Comments |

Create a new Sheet tab within an existing Google Sheet file and format a header row (font size, colour, alignment, etc) for appending data to.

Header row

Header row

The following Google Apps Script was developed as part of a larger tool for collating file information into a specifically formatted Google Sheet. As the tool was an Add-on I needed to setup the Google Sheet file in advance so that information could be later appended to it. This code:

  • Checks if a particular named Sheet (tab) already exists within the Google Sheet file. If it does then it will delete the existing Sheet and create a brand new one.
  • Reduce the overall number of columns within the Sheet.
  • Insert 1 row of data to use as the Header row.
  • Set the Header row font size, colour, weight (bold) and horizontal/vertical alignment.
  • Set the Header row background colour, height and column widths.

Source: The Gift of Script: Create a new Sheet tab and format a header row

Comment, Solutions, Gmail, Sheets, V8, Beginner

r/GMail – Has anybody here tried to improve Martin Hawksey’s free Mail Merge system? [adding custom attachments to mailing] 

| | 0 Comments |


Has anybody here tried to improve Martin Hawksey’s free Mail Merge system? from GMail

I’m not really a reddit user but this question about adding custom attachments my Google Workspace Solutions Gallery – Mail Merge example landed in my inbox. The solution is, in Google Apps Script tradition, one line of code! The line is:

if(row['Attachment'] != '') emailTemplate.attachments.push(DriveApp.getFileById(row['Attachment']).getBlob());

The logic is:

  1. check if the row value in the column named Attachment 
  2. if true call Drive to open file with attachment id and push to the emailTemplate object.

The line is added just after:

const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

If you prefer you can also copy this sheet for a working example. To attach different files the Attachment row cell needs to include a Google Drive file id. There are a number of ways you can get Drive file id’s. For example, here is a Getting all files’ file-id from a folder in Google Drive snippet.

If you are interested in more of what is happening in this solution, the emailTemplate object is returned by getGmailTemplateFromDrafts_(). This function gets the Gmail draft message and as well as any attachments including inline images. If you are interested in handling attachments and inline images you can read more in Everything you ever wanted to know about Gmail draft inline images and Google Apps Script but were afraid to ask.

Community News, Beginner

Getting started with Google Apps Script – Aryan Irani

Tags:
| | 0 Comments |

Image credit: Aryan Irani

In this course, we will be covering the basics of Google Apps Script and working our way by working with Google Sheets, Google Workspace API’s and more. After working with variables, operators, loops in Google Apps Script, we will be working with Google Sheets, where we will start out by getting and setting values in Google Sheets, followed by doing some amazing projects in Google Sheets and Google Apps Script. After working with Google Sheets, we will work with Google Forms, Google Classroom, YouTube and more.

It’s always pleasing to see new Google Apps Script courses being published. The latest one we’ve spotted has been created by Aryan Irani and hosted on Udemy. We’ve not taken this course but the landing page contains an overview and previews of some of the video content.

Source: Getting started with Google Apps Script – Aryan Irani

Solutions, Sheets, Beginner

2-Way sync between sheets without a unique ID

Tags:
| | 0 Comments |

How to setup 2-way sync within the same workbook without any unique ID.

I’m in the habit of adding IDs to sheets where I need to do lookups, but sometimes, I inherit a sheet without IDs in place (or I can’t edit the sheet structure). Spencer outlines a clever way to handle syncing between two sheets without using IDs in this video.

Source: 2-Way Sync: No Unique ID

Solutions, Forms, Beginner

Adding Members to a mailing list using an API, Google Forms and Google Apps Script

Tags:
| | 0 Comments |

Image credit: Baba Funke

I decided to explore something similar to a Webhook for Google Forms. In other words, something that triggers another action when a Google form is submitted. In this case, when a Google form is filled and submitted, the results should be sent to an API endpoint for adding members to a database. I found an answer in Google Apps Script, a coding language based on Javascript which allows for programmatically extending Google Apps like Google Forms, Sheets, Docs etc.

A nice explanation of how triggers can be used in Google Forms to push new data to other systems. The post is easy to read and includes lots of screenshots to help prevent you getting lost.

Source: Adding Members to a mailing list using an API, Google Forms and Google Apps Script

Solutions, Sheets, Beginner

How to Fetch Employee Data from Workday by Building the Great Deku Tree Inside of Google Sheets

Tags:
| | 0 Comments |

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.

Ever wondered how people reporting was handled at Twitch? Max Brawer, Head of People Analytics at Twitch, brings his ‘A’ game outlining how a little Google Apps Script and some Workday API wrangling can be used for some fun reporting. You can read more in the source link and here’s Max’s old post on The Endless Possibilities of Freeing Your Data from Workday and Putting it into Google Sheets: a How-to.

Source: How to Fetch Employee Data from Workday by Building the Great Deku Tree Inside of Google Sheets