AppsScriptPulse

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

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

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

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

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…

Automate onboarding of company resources from a Google Sheet – Sheets to Apps

In this episode of Sheets to Apps, we cover how to give newcomers access to resources via Google Forms and an Apps Script that lives in a Google Sheets, making on-boarding new employees simple and easy.

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

Google Apps Script Patterns: Writing rows of data to Google Sheets the V8 way

For some context for seasoned Google Apps Script developers like me until V8 we’ve been working in a bit of an old school JavaScript bubble. With V8 there is an opportunity to write less verbose and more succinct code. To see some of the differences below I’ve copied the Writing JSON data mapped to row headings example from my original post and updated this with a V8 version and will highlight the changes.

This post covers a range of techniques and includes a typical pattern for writing structured data like JSON responses from third party sources.

Source: Google Apps Script Patterns: Writing rows of data to Google Sheets the V8 way

How to Produce Quality Titles & Meta Descriptions Automatically

Here’s how you can produce quality titles and meta descriptions automatically in Google Sheets with minimal Python and JavaScript code.

A thoughtful implementation of custom functions in Spreadsheets using Google Apps Script.

Source: How to Produce Quality Titles & Meta Descriptions Automatically

Starting With Google Script

At the highest level, Google Apps Script (GScript for short), is a scripting platform for light-weight development across the entire Google Suite ecosystem. Each GSuite “app” (Sheets, Docs, Calendar, etc…) is referred to as a GScript service. Each service, such as the Google Sheets service we’ll strictly focus on throughout this article, is made up of multiple classes — each with the typical class structure of properties & methods.

You’re likely using Google Sheets already, so why not take a few minutes to learn how to customize & automate it to your particular needs? With Google Script, Google has made it extremely easy for anyone to extend Sheets.

Source: Starting With Google Script

Import Data from Google Sheets to Firestore using Google Apps Script

Cloud Firestore is a fast, fully managed, serverless, cloud-native NoSQL document database that simplifies storing, syncing, and querying data for your mobile, web, and IoT apps on a global scale.

In this post, you will learn how to take the data from Google Sheets and store those records in Firestore using Google Apps Script

Source: Import Data from Google Sheets to Firestore using Google Apps Script

Understanding Macros and Apps Script

Macros are small programs that a Google Sheets user can create to complete repetitive tasks. In fact, macros are an excellent way for a user to become familiarized with Apps Script, as a snippet of code is saved in the script editor each time a macro is recorded. Recording a macro is as simple as going to Tools → Macros → Record macro.

Recording macros and assigning shortcuts to them is a great way to execute repetitive tasks and become a more efficient Sheets user. You can also import macros from other workbooks so you can perform the same tasks and assign the same shortcuts across all of your workbooks.

Source: Understanding Macros and Apps Script

How to Send SMS Messages with Google Sheets and your Android Phone – Digital Inspiration

Send personalized text messages to your contacts with the help of Google Sheets and your Android phone. The SMS messages go directly from your phone SIM, no third-party SMS service is required.

Amit Agarwal (@labnol) has highlighted a really interesting model to integrate G Suite with Android mobile apps using Google Apps Script and MIT’s App Inventor. In Amit’s example he illustrates how you can quickly create your own Android app as a proxy for sending SMS messages from a Google Sheet. Given the flexibility of App Inventor this opens up numerous other opportunities to integrate G Suite and your Android phone.

Source: How to Send SMS Messages with Google Sheets and your Android Phone – Digital Inspiration

Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

Have you ever woken up in the middle of the night thinking – phawh! I’d really like to know how many words there are in a paragraph – Or stopped mid-chew on some arbitrary item carelessly left in the paddock by the boss and thought – geez! If there was only a way to count the number of sentences in a paragraph in a cell in Google Sheets!?

In this short tutorial, Yagisanatode will show you how to get the total count of words in a cell and the total count of sentences in a cell in Google Sheets. They even have a how-to on getting the average sentence length in a paragraph.

Source: Google Sheets: Get the Number of Words or Sentences in a Cell (Paragraph) – Yagisanatode

Merging two 2D arrays/datasets/tables with Google Apps Script – Andrew Roberts

Ever needed to merge two similar datasets and automatically get rid of any duplicates?

This article describes how to do that using the SsObjects Google Apps Script library.

Source: Merging two 2D arrays/datasets/tables with Google Apps Script – Andrew Roberts

Scrape and save data to Google Sheets with Apps Script

If you are looking for a way to get data that is displayed on a website and save it to a Google Sheet every <some time interval>—because a record number of cmd+x and cmd+v keystrokes aren’t really the stats that resonate with you and your friends—please, read on.

A detailed guide by Kamie Robinson on how to scrape data from a website, tabulate that on a Spreadsheet and a sweet tip to backfill your data!

Source: Scrape and save data to Google Sheets with Apps Script

Track Coronavirus (COVID-19) Cases in India with Google Sheets – Digital Inspiration

The Coronavirus (COVID-19) tracker uses sparklines in Google Sheets to help you visualize the spread of the coronavirus outbreak across different states of India over time.

You can always depend on Amit Agarwal (@labnol) to come up with clever solutions. In this post Amit outlines how he uses Google Apps Script to collect/parse data from the Indian Government website so you can track COVID-19 cases. Already Andrew Roberts has used Amit’s solution to create another version of the tracker for Wales (UK). Ping us at @AppsScriptInfo if you make a variation for your own region and we’ll list it in this post.

Source: Track Coronavirus (COVID-19) Cases in India with Google Sheets – Digital Inspiration

Tuning your Google Apps Script experience (for Google Sheets on steroids)

Are you automating Google Sheets using Google Apps Script? Automation is awesome but doing it with the Google tools can be cumbersome.

Interesting article by José Miguel Gutiérrez that provides some amazing tips on how to improve your coding experience while working with Spreadsheets and Google Apps Script.

Source: Tuning your Google Apps Script experience (for Google Sheets on steroids)

Subscribe to Apps Script Pulse...