AppsScriptPulse

Automate Adding Members to Google Group from Google Sheets — Google Apps Script

I added a script to Google Sheets that auto added new email addresses to the Google Group every time the sheet was edited.

The author was using Google Group to provide access to their dashboard in Google Data Studio. The members kept adding and the process became tedious. They were already keeping/adding all emails in a Google Sheet for some mapping. So this is what they did.

Source: Automate Adding Members to Google Group from Google Sheets — Google Apps Script

Keyword Suggestions using the Google Suggest API and Google Apps Script

For a company looking to improve its online presence, the keywords they rank for online, and their position, are crucial. Knowing which keywords to target can be difficult, especially for a smaller business that may not yet rank for many, or established businesses looking to find additional terms they may be overlooking.

One basic tool to hunt for new keywords to rank for is Google autocomplete – the process of simply beginning to type in the Google search box and letting Google suggests possible search terms based on what you’ve input. These suggested terms are based on what other people are searching for using your input term and therefore may provide useful keyword suggestions. This is an introductory blog post on how to utilise the Google Suggest API for keyword suggestions using google apps scripts to pull the data.

Source: Keyword Suggestions using the Google Suggest API and Google Apps Script

Parsing JSON in Google Sheets with Zendesk and Google Script

For the most part, using ImportJSON works well—it’s a low effort way of pulling data from any API’s GET endpoint and storing that data in a sheet for further parsing and filtering. But, there was a flaw: Zendesk’s API calls are paginated and limited to 100 results per page. That meant that we needed to add a new ImportJSON() formula every 100 rows to get all of our data.

This follow-up guide goes the extra step—not to feed a big JSON file into ImportJSON, but to write a script that does everything, including pagination. And, as an added bonus, is easier on your API rate limit.

Source: Parsing JSON in Google Sheets with Zendesk and Google Script

SHEETSCON 2020 – Free Online Conference for Google Sheets Users, 11-12 March, 2020 

SHEETSCON 2020

Come and celebrate Google Sheets at SheetsCon 2020, the world’s first, dedicated online conference for Google Sheets users.

Listen to world-class experts talk about how they craft solutions using Google Sheets and G Suite.

Over the course of two jam-packed days, you’ll hear everything from deep-dive tutorials on specific topics to cutting-edge solutions across different industries. You’ll have the chance to network with other professionals and meet vendors at the forefront of the Google Sheets movement.

Looks like a great event being hosted by Ben Collins for Google Sheets users. Click through to the SheetsCon 2020 website for the full agenda and free registration

Source: SheetsCon 2020

 

Bulk add unique data to an SQL Table in Apps Script

The following code combines Google Apps Script and SQL to bulk insert a Google Sheet of data into an existing SQL Table, but only after checking the record does not already exist so that it can be safely re-run.

Source: The Gift of Script: Bulk add unique data to an SQL Table in Apps Script

Email scheduler for Gmail – overview

Email scheduler spreadsheet columns image

Email scheduler spreadsheet columns

Over the next few blog posts I look to explore my development of an email scheduler for Gmail that is designed to let you schedule when you want an email composed in your drafts to be sent (by specifying the date/time).

This was written prior to Google introducing an email scheduler themselves.

Source: The Gift of Script: Email scheduler for Gmail – overview

Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins

Every Monday I send out a Google Sheets tip email and occasionally I’ll include a formula challenge…. It would have been too time consuming to reply to all 150 responses manually from my inbox. Since 95% of all my replies would be the same … it was a perfect case for automation.

Ben Collins expertly shares how he automates part of his Gmail inbox to review emails in Google Sheets and prepare draft replies to be sent as responses. As always Ben provides details of each step and the code.

PS Don’t miss Ben’s free Automation With Apps Script course  – enrolment closes 31 January 2020.

Source: Gmail Mail Merge For A Specific Label With Apps Script – Ben Collins

conversational stats – a way to talk to your data

leverage the power of dialogflow & connect your data sources with simple apps script functions to experience the next-gen approach towards consuming data and shaping insights.

Nice proof of concept from AppsScriptPulse Contributor (and now Editor :)
Sourabh Choraria of the results of a recent hackathon to use Google Sheets as a data source for a Dialogflow agent/bot.

Source: conversational stats – a way to talk to your data

Storing data from the Flutter app → Google Sheets 📊

In this article, we’ll create a sample app which stores Feedback of user from the Flutter app into Google Sheets using Apps Script.

Source: Storing data from the Flutter app → Google Sheets 📊

Loop delete specific rows with counter

Example table with specific rows to delete

Example table with specific rows to delete

Table with row 3 now deleted and data shifted up

Table with row 3 now deleted and data shifted up

The following Google Apps Script has been created to help with a recent difficulty I was having with deleting rows containing a specific value, where the loop was losing the row position due to the spreadsheet data all shifting up a row each time one was deleted. Some searching online suggested looping through a spreadsheet in reverse (from bottom up) to avoid the shift, but that seems more like a workaround than a direct solution …

Source: The Gift of Script: Loop delete specific rows with counter

Subscribe to Apps Script Pulse...