AppsScriptPulse

Solutions, Calendar, V8, Intermediate

Are the benefits of automation worth the overhead of setting things…

Tags:
| | 2 Comments |

Image credit: Brightec

Why setting up automation is always worth it

As developers, we often find ourselves weighing up the benefits of automation against the overhead of setting things up. This is true for our personal workflows as well – I often have an idea of how I could automate various arduous tasks, but in reality, it’s difficult to justify doing so.

I ended up going with my gut on this one, and built a ‘calendar sync tool’ that automates the process. It turned out to be 100% worth it – and in this article, I’m going to argue that it’s always worth automating!

This post from Jonny Townend at Brightec is a two-for-one both highlighting the benefits of taking the time to automate workflows and as a bonus sharing a Google Apps Script powered Google Calendar sync tool.

Source: Are the benefits of automation worth the overhead of setting things…

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.

Solutions, Docs, Forms, Sheets, V8, Intermediate

Pseudo OnEdit Trigger for Google Document using Google Apps Script

Tags:
| | 2 Comments |

In the current stage, there is not OnEdit trigger for Google Document. But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.

Source: Pseudo OnEdit Trigger for Google Document using Google Apps Script

Solutions, Drive, Sheets, V8, Intermediate

How to Automatically Add Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Tags:
| | 0 Comments |

Learn how to instantly share newly enrolled teachable students to Google Drive files and folders with Google Sheets and Apps Script.

Scott ‘Yagi’ Donald provides a very thorough walk-through of how he has connected a webhook from a third party service to Google Sheets to share specific Google Drive folders and files with named users. This post is well worth a visit as it contains a number of tips and tricks like using Google Forms to follow-up with user email addresses that are not associated with a Google account.

Source: How to Automatically Share Teachable Students to Google Drive Files and Folders when they Enroll in your Course – Yagisanatode

Comment, Solutions, V8, Intermediate

How to get Google Lighthouse reports using PageSpeedInsights API and Google Apps Script

Tags:
| | 2 Comments |

Lighhouse

Image: CC-BY Google

Lighthouse is an open-source, automated tool for improving the quality of web pages. You can run it against any web page, public or requiring authentication. It has audits for performance, accessibility, progressive web apps, SEO and more.

On StackOverflow there is a nice question/answer from Mert Dökümcü on How to send Google Lighthouse reports to Slack using PageSpeedInsights API and Google Apps Script. You can read the full solution there and below is an excerpt from Mert’s post (copied here CC-BY-SA Mert Dökümcü) of the bit that really interested me, getting results from PageSpeedInsights.

First and foremost, get…

Once you have these, go to https://script.google.com/home and create a new script. The following code should do the trick:

var mobileData = fetchDataFromPSI('mobile');
var desktopData = fetchDataFromPSI('desktop');

function pageSpeedApiEndpointUrl(strategy) {
  const apiBaseUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed';
  const websiteHomepageUrl = ''; // Your website
  const apikey = ''; // Your API key
  
  const apiEndpointUrl = apiBaseUrl + '?url=' + websiteHomepageUrl + '&key=' + apikey + '&strategy=' + strategy;
  return apiEndpointUrl;
}
function fetchDataFromPSI(strategy) {
  const pageSpeedEndpointUrl = pageSpeedApiEndpointUrl(strategy);
  const response = UrlFetchApp.fetch(pageSpeedEndpointUrl);
  const json = response.getContentText();
  const parsedJson = JSON.parse(json);
  
  const lighthouse = parsedJson['lighthouseResult']
  const originLoadingExperience = parsedJson['originLoadingExperience']

  const result = {
    'overall_performance': originLoadingExperience['overall_category'],
    'score': lighthouse['categories']['performance']['score']*100,
    'firstContentfulPaint': lighthouse['audits']['first-contentful-paint']['displayValue'],
    'speedIndex': lighthouse['audits']['speed-index']['displayValue'],
    'timeToInteractive': lighthouse['audits']['interactive']['displayValue'],
    'firstMeaningfulPaint': lighthouse['audits']['first-meaningful-paint']['displayValue'],
  }
  return result; 
}

Solutions, Groups, V8, Intermediate

Checking Google Groups membership recursively using Google Apps Script

Tags:
| | 0 Comments |

Learn how to check indirect membership of users or groups in the context of the Google Groups Service using Apps Script.

This is a great post if you want to learn about how to programmatically navigate the hierarchy of Google Groups, which may also come in handy when you are processing other tree like structures often found in JSON. All the code needed to run this is provided and clearly explained.

Source: Getting Google Groups membership recursively using Apps Script

Solutions, Sheets, Web App, V8, Beginner

Letting users run a Google Apps Script on Google Sheets without authorizing scopes and showing the source code

Tags:
| | 0 Comments |

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The post highlights the use of the existing =IMPORTML() Google Sheets function and a publish webapp to run a Google Apps Script without the end user having to authorize the script. A limitation of this approach, and also important warning to keep in mind, is the deployed web app needs to be deployed to run as the script owner and by anyone who has the web app link.

Source: Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

Solutions, Docs, V8, Intermediate

How to Delete Blank Rows from Tables in Google Documents – Digital Inspiration

Tags:
| | 0 Comments |

How to remove all blank rows from one or more tables in a Google Docs document with Google Apps Script. You may also delete blank rows from tables in Google Slides.

Source: How to Delete Blank Rows from Tables in your Google Documents – Digital Inspiration

Solutions, Docs, V8

Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script – Yagisanatode

Tags:
| | 0 Comments |

Learn how to append a date-time stamp to list items, paragraphs and more in Google Docs with Google Apps Script

A very slick example from Scott ‘Yagi’ Donald which shows a very simple method for appending text at the current cursor position and all achieved in just over 50 lines of code, with comments. Head over to the source for the full code and explanation.

Source: Append List Items, Paragraphs and table cell items with a date-time stamp in Google Docs using Google Apps Script – Yagisanatode

Add-on, Solutions, Gmail, V8, Intermediate

Mergo: Designing a template gallery in a Google Workspace add-on

Tags:
| | 0 Comments |

Mergo is a mail merge add-on for Gmail. With the new gallery, users can easily create, share & reuse templates.

Instead of starting your email from scratch, you can open the gallery and select a template: it will appear as a draft in Gmail.

We recently had Romain Vialard join us on Totally Unscripted to talk about some of his add-ons. This post is another example of Romain’s continual exploration of Apps Script, this time looking the new Grid view for the CardService. You an find out more in the source link.

Source: Mergo: Designing a template gallery in a Google Workspace add-on