AppsScriptPulse

Official News, Chat

Google Chat developer platform release notes – new Chat APIs generally available

Tags:
| | 0 Comments |

Video credit: Pablo Felip Monferrer / @pfelipm

Build Chat apps that bring your services and resources right into Google Chat, letting users get information and take action without leaving the conversation.

Google has been spending a lot of effort recently adding features to Google Chat. This just hasn’t been limited to the user interface and Google recently announced a long list of new Chat API methods are now generally available. For Google Workspace developers and administrators this opens up more opportunities to manage chat messages, spaces and membership.

For Apps Script developers I think it’s unlikely you’ll see a ChatApp or a Chat Advanced Service, but with an OAuth2 library you’ll be able to make calls to the Chat REST API. With the new Google Chat API methods comes the ability to access user data and perform operations on the authenticated user’s behalf. I don’t think it will take long before we see example solutions appearing from the community and given this video from Pablo Felip Monferrer I’ll be keep a close eye on his blog pablofelip.online.

Source: Google Chat developer platform release notes  |  Google for Developers

Community News, Library, Solutions, Sheets, Intermediate

Management of rich text cell formatting in Google Sheets with Google Apps Script

Tags:
| | 0 Comments |

In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

Some very clever work from Kanshi Tanaike which can help with the management of cell text formatting in Google Sheets.  The RichTextAssistant Apps Script library included in the post has some nice methods for both handling and preserving cell text formatting. It’s worth spend a little time checking the various samples provided to see what is possible and how these might enhance one of your own script projects.  If you are curious the source code for the library is also on GitHub and linked from the post.

Source: Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script

Solutions, Sheets, Intermediate

Two ways to remove duplicate rows from Google Sheets using Google Apps Script

Tags:
| | 0 Comments |

For many years users had to find a variety of workarounds if they wanted to remove duplicate rows from Google Sheets. This all changed in 2019 when Google announced new features, which included removing duplicates from Google Sheets. Recently I got tagged in a conversation with Andrea Guerri who shared some ‘remove duplicate’ example scripts. This sent me down a bit of a rabbit hole looking at various ‘remove duplicate’ Apps Script solutions and I’ll share two of my favourites.

Source: Two ways to remove duplicate rows from Google Sheet using Google Apps Script

Solutions, Sheets, Intermediate

How to monitor your MongoDB with Google Sheets and Apps Script 

Tags:
| | 0 Comments |

Conduct app monitoring, data backup, and data analysis for your MongoDB … This article shares a simple way to achieve this using Google Sheets and Apps Script. In this article, you will learn how to:

  1. Populate MongoDB data in Google Sheets for analysis
  2. Receive daily reports of your app’s growth metrics via email

Often the hardest part of interacting with other services using Google Apps Script is working out the payload you need to send with UrlFetchApp. Despite having almost 1,000 posts in AppScriptPulse, this is the first post featuring MongoDB. The source post has all the code and setup instructions to start collecting MongoDB data into Google Sheets as well as sending a daily email report of selected metrics.

Source: How to Track Your App’s Growth Using Google Sheets and Apps Script

Solutions, Sheets, YouTube, Intermediate

Building a YouTube comments dataset with Google Apps Script

Tags:
| | 0 Comments |

The first step in conducting research involves acquiring an appropriate dataset. .. Apps Script is a scripting platform developed by Google, that provides a user-friendly interface that enables easy automation and interaction with various Google services, including YouTube’s API.

For many years I was custodian of TAGS, a Google Sheets solution to archive Twitter searches. This came to an abrupt end when Twitter put a hefty paywall on API access. I’m sure there were ‘bad actors’ using TAGS, but I was also aware there were a number of academics and students using this solution to help make the world a little better. As noted in this source post “Dataset plays a crucial role in ensuring the accuracy and dependability of the results we obtain”.

For social scientists looking for new datasets this post from Randie Pathirage highlights how you can use Google Apps Script to get comments on YouTube videos using the YouTube Data API.

Source: YouTube Comment Scraping Made Easy with Apps Script

Official News, Solutions, Drive, Sheets, Beginner

Generate and send PDFs from Google Sheets with Google Apps Script 

Tags:
| | 0 Comments |

Automatically create PDFs with information from sheets in a Google Sheets spreadsheet. Once the PDFs are generated, you can email them out directly from Sheets. This solution focuses on creating custom invoices, but you can update the template and script to fit your needs.

Today as part of a customer Google Apps Script introduction workshop, we covered a basic invoice / PDF example. By coincidence this month’s Google Workspace Developers Newsletter highlighted a solution spotlight from the Apps Script samples catalogue which does something very similar.

A trick I learned today from our Lead Workspace Trainer, Tim McLardy, was rather than using UrlFetchApp to get a PDF version of the Google Sheet with the export/?format=pdf url, is instead getting the Google Sheet as a blob and creating a pdf with DriveApp.createFile(). As this technique will generate a PDF with pages with all your Sheet tabs the trick is to selectively hide all but the tabs you want in your PDF. In the case of the example linked as the source post you can rewrite the createPDF() function as:

/**
 * Creates a PDF for the customer given sheet.
 * @param {string} ssId - Id of the Google Spreadsheet
 * @param {object} sheet - Sheet to be converted as PDF
 * @param {string} pdfName - File name of the PDF being created
 * @return {file object} PDF file as a blob
 */
function createPDF(ssId, sheet, pdfName) {
  // Based on https://webapps.stackexchange.com/q/162155/30021
  const ss = sheet.getParent();
  const sheets = ss.getSheets();
  // Hides all sheets that are not the main one
  sheets.forEach(s => {
    if (s.getSheetName() !== INVOICE_TEMPLATE_SHEET_NAME) {   
      s.hideSheet()
    }
  });
  
  // make a pdf of the sheet
  const folder = getFolderByName_(OUTPUT_FOLDER_NAME);
  const pdfFile = folder.createFile(ss.getBlob()).setName(pdfName);
  
  // show all the sheets
  sheets.forEach(s => s.showSheet())
  return pdfFile;
}

Source: Generate & send PDFs from Google Sheets  |  Apps Script  |  Google Developers

Library, Solutions, Intermediate

Generating XML and HTML from JSON objects using Google Apps Script

Tags:
| | 0 Comments |

A useful library to create prettified HTML, XML GraphML and other markups directly from Apps Script or JavaScript.

A long, long, long time ago Apps Script had a Xml class which was deprecated in favour of the current XML Service. One of the nice features of the old Xml class was the Xml.parseJS() method, which “given a JavaScript array … returns an XmlDocument representation”.

The current XML Service doesn’t have a JavaScript to XML parser, but Bruce Mcpherson has recently shared a library that can be used to easily convert JSON objects into XML and it’s many recognised formats like HTML. Bruce’s post shows how to can create various HTML page elements including head sections and tables. It’s also worth checking out Bruce’s other post on “Create GraphML markups from Apps Script” which is included in the source link.

Source: Markup HTML from JSON with Apps Script or JavaScript

Solutions, Sheets, Beginner

Listing free Udemy courses in Google Sheets with the Udemy API and Apps Script

Tags:
| | 0 Comments |

Learn how to use the Udemy API with Google Apps Script to find free programming courses on Udemy on any topic.

A double win in this post from Amit Agarwal. Not only can you get a list of free programming courses currently running on Udemy, but Amit includes the code snippet used to get/add these to a Google Sheet. This might be useful if you are working with other APIs and are looking for a code snippet to help format the data in a Google Sheets friendly format.

Source: Find Free Udemy Courses with Google Sheets and the Udemy API – Digital Inspiration

Official News, Solutions, Intermediate

A tool for exploring and testing Google Workspace APIs

Tags:
| | 0 Comments |

Image credit: Google

We recently launched the Google Workspace APIs Explorer, a new tool to help streamline developing on the Google Workspace Platform. What is this handy tool and how can you start using it?

The Google Workspace APIs Explorer is a tool that allows you to explore and test Google Workspace APIs without having to write any code. It’s a great way to get familiar with the capabilities of the many Google Workspace APIs.

The Google Apps Script editor bakes in some nice features including inline documentation to help when you are coding your script project (and if you don’t already know, next time you are in the online script editor press ctrl + space :). To help with discovery Google have recently published the Google Workspace APIs Explorer website, which lets you see and test a range of Google APIs. For Google Apps Script developers this site is a great way to help you understand how you can use the Advanced Services (my tip for Workspace admins is to check out the Directory and Report API which as part of the Admin SDK API Advanced Service).

Source: A Tool for Exploring and Testing Google Workspace APIs

 

Solutions, Gmail, V8, Beginner

Add the User’s Signature Block to an Automated Gmail Email with Apps Script – Yagisanatode

Tags:
| | 0 Comments |

 

Sending out emails as a part of a Google Workspace automated workflow is a very common task. In Google Apps Script we can send emails to users using the MailApp.sendEmail(), the GmailApp.sendEmail() method or even as a JSON payload with the Gmail Advanced API service.

While one might expect that the sender’s signature block would also be transmitted with the automated email, we find that this is not in fact the case.

Scott Donald highlights some of the pitfalls of trying to access the Gmail signatures using Google Apps Script. Fortunately Scott also shares a workaround for using a Gmail draft to store a number of signatures/snippets. This is all wrapped in a handy getSignatureBlock() function, created and shared by Scott to get either the user’s primary signature block or one stored in a draft.

Source: Add the User’s Signature Block to an Automated Gmail Email with Apps Script – Yagisanatode