AppsScriptPulse

Beginner, Sheets

How to combine arrays of data into one Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.

A little knowledge about data manipulation can get you a long way, particularly if you are doing lots of work in Google Sheets. This post from Baz Roberts is an opportunity to learn about spread syntax (also known as the spread operator) in arrays. Have a read and hopefully you’ll see the benefits.

Source: How to combine arrays of data into one sheet – this post is also available to read on bazroberts.com

Add-on, Solutions, Beginner

Restricting the number of times an account can use a feature in a Google Workspace Editor Add-on with Google Apps Script 

Tags:
| | 0 Comments |

One model of monetization for a Google Addon is to allow a certain number of free uses before restricting that feature. This post shows one way to restrict a feature in a Google Editor Addon sidebar.

John McGowan is continuing his Google Workspace Add-on development tips at pace. You can read the story so far on the Automagical Apps Blog. In the latest post you can find out how John uses the Properties Service, to record the number of times an account has used a feature in your add-on by communicating between the sidebar and Apps Script using google.script.run. A reminder as well that you can see how you can boost User Property read/write with the SpeedStore library.

Source: Restricting the number of times to use a feature in a Google Addon Sidebar | Automagical Apps Blog

Add-on, Solutions, Beginner

Differentiating Google Workspace Add-on sidebar features based on a user licence property in Google Apps Script

Tags:
| | 0 Comments |

I always get requests as to how people can add a license to their Google Add-on. There are a few different steps and here I will show how you can share different information in the Sidebar based on a license status.

In Pulse we’ve previously featured a couple of community contributions on how to monetize your Google Workspace Add-On. Corentin Brossault’s How to monetize your Google Workspace add-on? provides some great code snippets and tips for handling user authentication and payment. We’ve also featured Riël Noterman’s solution for Using JWT as a license key in Google Apps Script Google Workspace Add-ons. This related  post from John McGowan (Automagical Apps) provides another piece to the puzzle demonstrating how you can use templates in HTMLService to switch user messaging based on stored user properties.

Source: Differentiating Google Addon Sidebar features based on a license | Automagical Apps Blog

Snippet, Beginner, Maps

Validate postal address with the new Google Maps Address Validation API and Google Apps Script

Tags:
| | 0 Comments |

 

Validate addresses using Google Maps Address Validation and Apps Script. Discover onleeaddress the add-on for Google Workspace.

I missed the announcement in November 2022 that there is a new Google Maps Address Validation API. I also missed this post from Stéphane Giron showing how you can use the Address Validation API in Google Apps Script.

The concept is simple, you provide the Address Validation API with what you think is a correct address and the API returns information on each component of the address and additional metadata. Visit the source post for more details on setup and some example code.

Source: Validate postal address with the new Google Maps Address Validation API and Apps Script

Snippet, Solutions, Sheets, Beginner

Find all matching values in Google Sheets cells with Google Apps Script

Tags:
| | 2 Comments |

Learn how to search and find all values in an entire Google Sheet spreadsheet, in a specific sheet tab or in a range with Apps Script.

Handy little snippet from Scott Donald on using the Google Apps Script TextFinder class to find all the occurrences of matching text in a Google Sheet. The bit I particularly liked in this solution was how Scott used a .map method to return an object array of cell hits including A1 notation:

Array containing an object for each sheet containing the sheet name and the cell location (Image credit: Scott Donald)

Follow the source link for more information including a video tutorial.

Source: Find All Values in Google Sheets with Apps Script – Yagisanatode

Snippet, Beginner, Drive

Organise Google Drive files into a JavaScript object for use with Google Apps Script

Tags:
| | 0 Comments |

Organise Google Drive files into a JavaScript Object so that they can be looped through for each named individual.

Files to loop through and organise

Files to loop through and organise

The following Google Apps Script is designed to go through a folder of Google Drive files where a number of tutors have individual files for groups of students they teach. The code will collate all of the relevant files (specifically their IDs) that belong to each tutor, so that at a later date we can loop through the organised data set and create individual Google Sheets for each tutor and compile their student data into it.

What this code ultimately allows us to achieve is the ability to go through the files and get them organised for further coding.

In this example we have a file name pattern of ModuleCode – Tutor Name – Group Number for example ABC – Jane Doe – Grp 02. So we want to collate all of the files that belong to Jane Doe first, then move on to the next tutor (Micky Mouse in this example).

The collated data will look like this:

{
   "Jane Doe":[
      "FILE ID HERE",
      "FILE ID HERE",
      "FILE ID HERE"
   ],
   "Micky Mouse":[
      "FILE ID HERE",
      "FILE ID HERE"
   ]
}

Source: The Gift of Script: Organise files into a JavaScript Object

Solutions, Drive, Sheets, Beginner

Bulk add files to existing Google Drive folders using Google Apps Script

Tags:
| | 0 Comments |

Take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.

Bulk add files to existing Google Drive folders

Bulk add files to existing Google Drive folders

The following Google Apps Script tool is designed to take a Google/Shared Drive folder full of files that you want to make a copy of and place into another set of existing Drive folders.You can decide if you want the subfolder name appending/prepending/ignoring in the file name of the new copy to help make it unique.

The tool will iterate through a single level of folders (not sub-subfolders) and for each one place a copy of the files into it.

Source: The Gift of Script: Bulk add files to existing Google Drive folders

Solutions, Drive, Sheets, Beginner

Bulk combine CSV files into a single Google Sheet with Google Apps Script

Tags:
| | 0 Comments |

Bulk combine multiple CSV files into a single Google Sheet, putting the data in specific locations and creating multiple tabs.

Bulk combine CSV files into a single Google Sheet

Bulk combine CSV files into a single Google Sheet

The following Google Apps Script is designed to take a number of CSV files (structured in an identical way) stored in a Google Drive folder and combine them all into a single Google Sheet file. It maps the data within the CSV file to specific cells in the Google Sheet, along with creating a tab for each of the CSV files to separate them out.

Source: The Gift of Script: Bulk combine CSV files into a single Google Sheet

Solutions, Sheets, Beginner

Get hidden or visible Google Sheet tabs with Google Apps Script

Tags:
| | 0 Comments |

Learn how to list hidden Google Sheets tabs with Google Apps Script – CODE and VIDEO tutorial with script explainers.

This post from Scott Donald is an opportunity to learn about how you can work with hidden tabs in Google Sheets with Apps Script. For beginners this post is also an opportunity to learn about the JavaScript reduce method as a way to iterate through data. As well as the code and supporting video the author Scott Donald has some related projects where you can see this solution in action.

Source: Get Hidden or Visible Google Sheet Tabs with Google Apps Script – Yagisanatode

Community News, Beginner

Tech Recruiter Tries Coding pt. 2 — Google Apps (Type)Script

Tags:
| | 0 Comments |

Image credit: Unknown

Talking CRMs, Databases, and how to ditch them; this time within the Lead Generation domain. Bonus: pitfalls of a 90s computing mindset! 😅

Previously on Pulse we shared Rafael Romo Mulas’ post, Tech Recruiter Tries Coding. Rafael has recently published part 2 in which they highlight their journey into VS Code, clasp and TypeScript. If you are unfamiliar with TypeScript its a language which transpiles into JavaScript and basically designed to help you write better code. Rafael notes:

… the main difference is you are forced to be more precise, which avoids running into stupid errors! Therefore, to my surprise, it is actually easier to code in TypeScript than JavaScript, even more so for a beginner like me, because:

  • There is not much to “unlearn” from JS, if you just started anyway.
  • You’re alerted when values can be of a type that would cause errors.
  • You’re forced to do something about it, basically debugging in advance.

You can read more about Rafael’s journey in the linked article

Source: Tech Recruiter Tries Coding pt. 2 — Google Apps (Type)Script