AppsScriptPulse

Category: Sheets

SheetsSolutions

Parsing JSON in Google Sheets with Zendesk and Google Script

Tags: , ,
| |

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

Community NewsSheets

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

Tags: ,
| | 0 Comments |

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

 

GmailSheetsSnippet

Email scheduler for Gmail – overview

Tags:
| |
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

GmailSheetsSolutions

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

Tags: ,
| | 0 Comments |

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

SheetsSolutions

conversational stats – a way to talk to your data

Tags: ,
| | 0 Comments |

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

SheetsSnippet

Loop delete specific rows with counter

Tags:
| |
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

SheetsSnippetSolutions

Data Validation – check for blank cells

Tags:
| | 0 Comments |

The following Google Apps Script code was developed as a tool to check that all the necessary data existed in a spreadsheet before another function was triggered. The reason for this was that if any data was missing it would affect the student Group files I was creating (eg no email address or name) that would eventually prevent further functions from running at a later date.

Source: The Gift of Script: Data Validation – check for blank cells

GmailOfficial NewsSheetsSolutions

Create a mail merge using Gmail and Google Sheets | Sheets to Apps

Tags:
| | 0 Comments |

Whether you are in sales, marketing, education, project management, data analysis, etc., it’s common for people to want to send emails to multiple recipients with information that is customized to each. In this episode, learn about Martin Hawksey’s mail merge solution, which helps anyone send emails with customized content from a Google spreadsheet, and Gmail.

See the YouTube description for all the links you need.