JS map - Yagisanatode

List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script

If you have ever tried to get a list of all the child files and folders of a parent folder in Google Drive, you’ve no doubt discovered that it is a slow old process. Iterating over each item in a folder and then reading the metadata of that file or folder before calling the next one can take forever!

The built-in DriveApp Class for Google Apps Script is great for working on a small number of files and folders but it just doesn’t have the functionality to retrieve specific fields in your metadata, nor does the searchFiles method or searchFolders method have the ability to isolate just the fields that you want to retrieve. Subsequently, your processing time increases significantly as it ships junk data.

This tutorial is my attempt at, as the kiddies say today, creating a ‘blazingly fast’ file and folder iterator with Google’s Drive API v2 for Apps Script.

Continue reading “List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script”

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

Google Apps Script: WebApp, HtmlService, LockService; Google Sheets

In this tutorial, we are going to create an interactive story chain app that we can embed into a Google Site or your own site like WordPress.

What’s a chain story, Yagi?

Maybe you did this in school. Someone wrote the first part of a story. You then gave that story to someone else to continue writing. They then pass the story on to someone else to write the next part. And so on and so forth. In the end, the story is read out and everyone laughs at the direction the story went – except that one kid silently raging over their lack of control of the narrative.

Why are we making this? How’s it going to help me?

Well, for one, I thought it would be fun. More importantly, this will allow us to have a look at how Google Apps Scripts communicates client to server-side and vice versa in a little more advanced environment than our previous tutorial. It will also give us an opportunity to look at some more parts of Google Apps Script as they relate to creating a WebApp.

Our chain story WebApp tutorial will also give us an opportunity to look at some of the pitfalls of using WebaApp. Particularly when using the execute as me permissions. Finally, this will then launch us into our follow-up tutorial on updating the WebApp to execute as the user rather than me, the owner of the app.

This tutorial is the second part of the WebApp series. However, if you can read a bit of JS, CSS and HTML, you should be able to follow along and if you get stuck you can always go back to the first tutorial:

Google Apps Script: How to create a basic interactive interface with Web Apps

Let’s get started…

The Example: An interactive chain story

Embedded below is our interactive Chain Story web app. If you are feeling creative, read the story so far and then add your part to the story. It has been written by readers just like you:

Continue reading “Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets”

Creating Unique Ranges from 2D Arrays in Google Apps Script

Google Apps Script, Google Sheets, Javascript

If you have ever done any work in Google Sheets you might have come across the incredibly useful UNIQUE function. This function can take a single row or multiple rows and generate a list of unique cell values.

Sometime you might have a use case where you need to replicate the UNIQUE functionality in Google Apps Script. Maybe you are drawing in data from another source like an API, BigQuery or a database, or presenting your unique data in another format.

Whatever the case, knowing how to to create a unique array from a 1-dimensional array or a 2-dimensional array is an important part of your toolkit as a coding goat 🐐. This tutorial will look at how to create this with modern Javascript that is incorporated into the Google Apps Script language. And of course, we’ll focus our tutorial more on a Google Apps Script because that’s kinda our thing.

We’ll start off with a very basic example of creating a unique array from a 1-dimensional array in our code:

…and then build ourselves up to a final example by grabbing multi-column range of Google Sheet data from which we create a unique list and then display the total sum corresponding to each unique cell value in each of our associated unique columns. Something like this:

UNIQUE 2-Array of multiple columns with result set in GAS — Click to Expand!

As we go through our examples I’ll display the runtime of the entire code. Runtime is not a perfect indicator of the performance of the script there are other factors that will influence the runtime, but it will do for our purposes. I think you will be surprised by how fast these scripts run.

Before we get started, let’s take a quick look at the sample data…

Continue reading “Creating Unique Ranges from 2D Arrays in Google Apps Script”

Find All Values in Google Sheets with Apps Script

Just like when using the Ctrl + F shortcut in Google Sheets to find values in your spreadsheet, there is a class in Google Apps Script that can do the same thing.

This could be a useful tool as a part of an automation process. For example, finding the location of a value and applying formatting to it or copying the cell’s entire data into a separate location if the value is a part of a larger text in the cell.

This tutorial accompanies the YouTube video tutorial of the same name.

Grab a copy of the starter sheet to play along and get the most out of the video.

Starter Sheet

Find All Apps Script – Starter Sheet

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The Code

Note that for the video tutorials, I have added the variables (e.g. the Sheet Name, Range) inside each function. It is usually good practice to keep these functions independent and call them from other functions. This makes them more reusable.

Instead, we can add the variables we need as parameters for the functions and return the result. See the example below:

/**
 * Activate all values in a range.
 * @param {string} text - the text to search for.
 * @param {string} sheetName - The name of the selected sheet.
 * @returns {array} Location cell of each selection. 
 */
function findAllItemsInASheet(text, sheetName){

  ...


  return locationList;
};


function runsies(){
  findAllItemsInASheet("koala", "Sheet1")
}

/**

* Activate all values in a range.

* @param {string} text - the text to search for.

* @param {string} sheetName - The name of the selected sheet.

* @returns {array} Location cell of each selection.

function findAllItemsInASheet(text, sheetName){

...

return locationList;

};

function runsies(){

findAllItemsInASheet("koala", "Sheet1")

}

These examples use the TextFinder class as a part of the Google Apps Script Sheets App Class.

In these examples, we use the findAll method of this class. This will return an array containing all the cells containing the selected value searched. From here, you can treat each cell as a range and call range methods like:

Get A1 Notation.
Get Sheet – Get Name.
Get Row.

We use the JavaScript Map method to iterate through each item that we find.

Find All Values in All Sheets

This finds all values in all sheets and returns an array containing an object for each sheet containing the sheet name and the cell location.

// Find value over all sheets. 
function findValueOverAllSheets(){
  const text = "koala";
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const textFinder = ss.createTextFinder(text)

  const allOccurrences = textFinder.findAll();

  const locationList = allOccurrences.map(item => {
    return {sheet:item.getSheet().getName(), cell: item.getA1Notation()}
    
  })

  console.log(locationList)
}

// Find value over all sheets.

function findValueOverAllSheets(){

const text = "koala";

const ss = SpreadsheetApp.getActiveSpreadsheet();

const textFinder = ss.createTextFinder(text)

const allOccurrences = textFinder.findAll();

const locationList = allOccurrences.map(item => {

return {sheet:item.getSheet().getName(), cell: item.getA1Notation()}

})

console.log(locationList)

}

Returns:

[ { sheet: 'Sheet1', cell: 'A3' },
  { sheet: 'Sheet1', cell: 'A4' },
  { sheet: 'Sheet1', cell: 'F14' },
  { sheet: 'Sheet1', cell: 'A16' },
  { sheet: 'Sheet2', cell: 'B4' },
  { sheet: 'Sheet3', cell: 'C6' },
  { sheet: 'Sheet3', cell: 'F8' },
  { sheet: 'Sheet3', cell: 'E15' } ]

[ { sheet: 'Sheet1', cell: 'A3' },

{ sheet: 'Sheet1', cell: 'A4' },

{ sheet: 'Sheet1', cell: 'F14' },

{ sheet: 'Sheet1', cell: 'A16' },

{ sheet: 'Sheet2', cell: 'B4' },

{ sheet: 'Sheet3', cell: 'C6' },

{ sheet: 'Sheet3', cell: 'F8' },

{ sheet: 'Sheet3', cell: 'E15' } ]

Find All Values in Selected Sheets

Finds all the values in a selected sheet and returns an array identifying the cell that each item is found.

// Find value over one sheet. 
function findValueOverOneSheet(){
  const text = "koala";
  const sheetName = "Sheet1";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName)

  const textFinder = sheet.createTextFinder(text)

  const allOccurrences = textFinder.findAll();

  const locationList = allOccurrences.map(item =>  item.getA1Notation())

  console.log(locationList)
}

// Find value over one sheet.

function findValueOverOneSheet(){

const text = "koala";

const sheetName = "Sheet1";

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName)

const textFinder = sheet.createTextFinder(text)

const allOccurrences = textFinder.findAll();

const locationList = allOccurrences.map(item => item.getA1Notation())

console.log(locationList)

}

Returns:

[ 'A3', 'A4', 'F14', 'A16' ]

Find All Values in a Selected Range

Finds the values for any item in a selected range and returns the row the item was found on.

Example 1

// Find Value over a range
function findValueOverARange(){
  const text = "koala";
  const sheetName = "Sheet1";
  const rangeLoc = "A2:A16";


  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(rangeLoc);

  const textFinder = range.createTextFinder(text);

  const allOccurrences = textFinder.findAll();

  const locationList = allOccurrences.map(item => {
    return  item.getRow();
  })


  console.log(locationList);
}

// Find Value over a range

function findValueOverARange(){

const text = "koala";

const sheetName = "Sheet1";

const rangeLoc = "A2:A16";

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const range = sheet.getRange(rangeLoc);

const textFinder = range.createTextFinder(text);

const allOccurrences = textFinder.findAll();

const locationList = allOccurrences.map(item => {

return item.getRow();

})

console.log(locationList);

}

Returns:

[ 3, 4, 16 ]

Example 2

function findValueOverARange_v2(){
  const text = "cheese";
  const sheetName = "Sheet4";
  const rangeLoc = "A1:A";


  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(rangeLoc);

  const textFinder = range.createTextFinder(text);

  const allOccurrences = textFinder.findAll();

  const locationList = allOccurrences.map(item => {
    return  item.getRow();
  })


  console.log(locationList);
}

function findValueOverARange_v2(){

const text = "cheese";

const sheetName = "Sheet4";

const rangeLoc = "A1:A";

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName(sheetName);

const range = sheet.getRange(rangeLoc);

const textFinder = range.createTextFinder(text);

const allOccurrences = textFinder.findAll();

const locationList = allOccurrences.map(item => {

return item.getRow();

})

console.log(locationList);

}

Returns:

[ 1, 2, 4, 5 ]

The Video

https://youtu.be/SM7qv5sPfPE

Find All in Google Sheets with Apps Script (https://youtu.be/SM7qv5sPfPE)

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.

How to Validate Specific Users on a Web App in Google Apps Scripts

You’ve created an awesome Google Apps Script web app for your secret society within your Google Workspace organisation or …dom! dom! DOM! … the world. The problem is that you only want to share your web app with the worthy. Those selected few. 🐐🛐🛐🛐

How do you do this? How to prevent this most coveted of apps from reaching the wrong hands?

It’s actually surprisingly simple.

In this tutorial, we will explore how to validate selected users to provide access to your web app. For our example, we validate users based on whether or not they have edit access to a Google Drive file ( a common occurrence). In the discussion, we will also look at alternative ways of validating emails.

One of the bonuses of the approach we will go through is that it can also be easily adapted for use in Google Workspace Add-ons, and Editor Add-ons like sidebars and dialogue boxes.

We’ll start off with an example and then move to a quick-use guide for those of you who just want to get in and apply the code to your own project. Then for those who want to know how it all works, I’ll dive into the details.

Let’s get started!

Continue reading “How to Validate Specific Users on a Web App in Google Apps Scripts”

Tag: JS map

List all files and folders in a selected folder’s directory tree in Google Drive: Apps Script

Like this:

Creating an embedded interactive Chain Story app with Google Apps Script and Google Sheets

The Example: An interactive chain story

Like this:

Creating Unique Ranges from 2D Arrays in Google Apps Script

Like this:

Find All Values in Google Sheets with Apps Script

Starter Sheet

The Code

Find All Values in All Sheets

Find All Values in Selected Sheets

Find All Values in a Selected Range

Example 1

Example 2

The Video

Like this:

How to Validate Specific Users on a Web App in Google Apps Scripts

Like this:

Share this:

Like this:

The Example: An interactive chain story

Share this:

Like this:

Share this:

Like this:

Starter Sheet

The Code

Find All Values in All Sheets

Find All Values in Selected Sheets

Find All Values in a Selected Range

Example 1

Example 2

The Video

Share this:

Like this:

Share this:

Like this: