Javascript map - Yagisanatode

Extracting the Valid Workdays Between Two Dates in JavaScript

Given a start date, end date, weekends and, holidays we will extract the valid workdays between two dates in JavaScript in this tutorial.

Sometimes we need to work backward from a date range and extract all the valid workdays within that range. Of course, we will need to exclude holidays and also days off for this period too so it is not just a simple case of subtracting the end date from the start date and adding one.

The best way to illustrate what we want to accomplish is by sharing some example data.

If you are looking to get the end work date given a start date and number of workdays, check out this tutorial:

Calculate the Workday End Date in JavaScript

The Example

For our example, we will need to prepare a list of date ranges, holiday periods and weekly days off (weekends).

We’ll say for convenience that we will work Monday to Friday and take Saturday and Sunday off. In JavaScirpt-land weekdays are represented as zero-based numbers with the USA-style start of the week occurring on Sunday. In this case, Sunday is zero (0) and Saturday is (6).

Let’s add in some holidays to give ourselves a well-earned break:

15 Sep 2024
31 Oct 2024 🎃🦇👻
24 Dec 2024
25 Dec 2024
01 Jan 2025

This could also be drawn from a regional API that provides dates for holidays in your area.

Finally, we will provide a small sample list of date ranges that we can check, but keep in mind that you can iterate over a much larger list at relatively good speed here.

10 Sep 2024 – 23 Sep 2024.
26 Oct 2024 – 4 Nov 2024.
22 Dec 2025 – 16 Jan 2024.

The blue represents the actual workdays, while the white section indicates the actual date range. Orange bold and underlined days are holidays that don’t fall on weekends. Light red days are weekly days off (in our case, weekends).

Across the top of the dates are the days of the week as a JavaScript day running from 0 (Sunday) to 8 (Saturday).

The Video Tutorial

To the video tutorial

Releases 8 Oct 2024

The Code

// EXTRACT THE WORKDAYS BETWEEN TWO DATES

// Given a start date, end date, weekends and holidays, extract the valid workdays between two dates. 


/**
 * Gets an array of workdays between two dates.
 * @param {Object} workperiod
 * @param {String} workperiod.startDate - Target date as valid JS date-string
 * @param {String} workperiod.endDate - Number of workdays. 
 * @param {Number[]} weekends - Array of days off as JS day of week. Sunday = 0, Saturday = 6
 * @param {String[]} holdays - Array of hoidays as JS-valid date-strings
 * @returns {Date[][]} Array of valid workdays.
 */
function getWorkdays(workperiod, weekends, holidays){
    const dayMilliseconds = 24 * 60 * 60 * 1000
    /**
     * Gets the UTC for current day.
     * @param {Sting} date 
     * @returns {Number} UTC Date in milliseconds
     */
    function getUTC(date){
        const dt = new Date(date)
        return Date.UTC(
            dt.getFullYear(),
            dt.getMonth(),
            dt.getDate()
        )
    }
    
    // Convert holidays of day of year as number. 
    const holidayUTC = holidays.map(holiday => getUTC(holiday))

    // Iterate over each date
    let workdays = workperiod.map(workday => {
        // -- Get day of year as number.
        const dayStart = getUTC(workday.startDate)
        const dayEnd   = getUTC(workday.endDate)

        // Get day of week
        let dayOfWeek = new Date(workday.startDate).getDay() 
        let day = dayStart

        let validWorkday = []
    
        while(day <= dayEnd){

            // Check if not weekends or holidays
            if(!weekends.includes(dayOfWeek) && !holidayUTC.includes(day)){
                const dt = new Date(day).toISOString().substring(0, 10)
                validWorkday.push(dt)
            }      

            day += dayMilliseconds
            dayOfWeek = dayOfWeek + 1 == 7? 0 : dayOfWeek + 1
        }

        return validWorkday

    })

    return workdays
}



/**
 * Main run test function
 */
function runsies(){
    // #### SAMPLE DATA 

    // HOLIDAYS
    const holidays = ["2024-09-15","2024-10-31","2024-12-24","2024-12-25","2025-01-01"]

    // DAYS OFF      [Sun: 0, Sat: 6] <- JavaScript Day of Week Standard.
    const weekends = [0, 6]

    const testRanges = [
        {
            startDate: '2024-09-10',
            endDate: '2024-09-23'
        },
        {
            startDate: '2024-10-26',
            endDate: '2024-11-04'
        },
        {
            startDate: '2024-12-22',
            endDate: '2025-01-16'
        },
    ]

    const respArray = getWorkdays(testRanges, weekends, holidays)

    testRanges.map((testRange, idx) => {
        testRange.validDays = respArray[idx]
        testRange.count = respArray[idx].length
    
        return testRange
    })
    
    console.log(JSON.stringify(testRanges,null, " "))

}

runsies()

100

101

102

103

104

105

106

107

// EXTRACT THE WORKDAYS BETWEEN TWO DATES

// Given a start date, end date, weekends and holidays, extract the valid workdays between two dates.

/**

* Gets an array of workdays between two dates.

* @param {Object} workperiod

* @param {String} workperiod.startDate - Target date as valid JS date-string

* @param {String} workperiod.endDate - Number of workdays.

* @param {Number[]} weekends - Array of days off as JS day of week. Sunday = 0, Saturday = 6

* @param {String[]} holdays - Array of hoidays as JS-valid date-strings

* @returns {Date[][]} Array of valid workdays.

function getWorkdays(workperiod, weekends, holidays){

const dayMilliseconds = 24 * 60 * 60 * 1000

/**

* Gets the UTC for current day.

* @param {Sting} date

* @returns {Number} UTC Date in milliseconds

function getUTC(date){

const dt = new Date(date)

return Date.UTC(

dt.getFullYear(),

dt.getMonth(),

dt.getDate()

)

}

// Convert holidays of day of year as number.

const holidayUTC = holidays.map(holiday => getUTC(holiday))

// Iterate over each date

let workdays = workperiod.map(workday => {

// -- Get day of year as number.

const dayStart = getUTC(workday.startDate)

const dayEnd = getUTC(workday.endDate)

// Get day of week

let dayOfWeek = new Date(workday.startDate).getDay()

let day = dayStart

let validWorkday = []

while(day <= dayEnd){

// Check if not weekends or holidays

if(!weekends.includes(dayOfWeek) && !holidayUTC.includes(day)){

const dt = new Date(day).toISOString().substring(0, 10)

validWorkday.push(dt)

}

day += dayMilliseconds

dayOfWeek = dayOfWeek + 1 == 7? 0 : dayOfWeek + 1

}

return validWorkday

})

return workdays

}

/**

* Main run test function

function runsies(){

// #### SAMPLE DATA

// HOLIDAYS

const holidays = ["2024-09-15","2024-10-31","2024-12-24","2024-12-25","2025-01-01"]

// DAYS OFF [Sun: 0, Sat: 6] <- JavaScript Day of Week Standard.

const weekends = [0, 6]

const testRanges = [

{

startDate: '2024-09-10',

endDate: '2024-09-23'

{

startDate: '2024-10-26',

endDate: '2024-11-04'

{

startDate: '2024-12-22',

endDate: '2025-01-16'

]

const respArray = getWorkdays(testRanges, weekends, holidays)

testRanges.map((testRange, idx) => {

testRange.validDays = respArray[idx]

testRange.count = respArray[idx].length

return testRange

})

console.log(JSON.stringify(testRanges,null, " "))

}

runsies()

The runsies() function

The runsies() function is the main test function. It contains the 3 different variables that we will send to the getWorkdays() function to generate our list of days.

We’re using the year-month-date (YYYY-MM-DD) format favoured by computer nerds like me.

Once we get the list of days back from the getWordays() function we will then map our existing testRanges array of objects and add the valid days as a property and the total count of valid days.

Finally, we will stringify the array object to make it pretty for logging.

You Are Looking So Fine Loki GIFfrom You Are Looking So Fine GIFs

Running the script will display these results:

[
 {
  "startDate": "2024-09-10",
  "endDate": "2024-09-23",
  "validDays": [
   "2024-09-10",
   "2024-09-11",
   "2024-09-12",
   "2024-09-13",
   "2024-09-16",
   "2024-09-17",
   "2024-09-18",
   "2024-09-19",
   "2024-09-20",
   "2024-09-23"
  ],
  "count": 10
 },
 {
  "startDate": "2024-10-26",
  "endDate": "2024-11-04",
  "validDays": [
   "2024-10-28",
   "2024-10-29",
   "2024-10-30",
   "2024-11-01",
   "2024-11-04"
  ],
  "count": 5
 },
 {
  "startDate": "2024-12-22",
  "endDate": "2025-01-16",
  "validDays": [
   "2024-12-23",
   "2024-12-26",
   "2024-12-27",
   "2024-12-30",
   "2024-12-31",
   "2025-01-02",
   "2025-01-03",
   "2025-01-06",
   "2025-01-07",
   "2025-01-08",
   "2025-01-09",
   "2025-01-10",
   "2025-01-13",
   "2025-01-14",
   "2025-01-15",
   "2025-01-16"
  ],
  "count": 16
 }
]

[

{

"startDate": "2024-09-10",

"endDate": "2024-09-23",

"validDays": [

"2024-09-10",

"2024-09-11",

"2024-09-12",

"2024-09-13",

"2024-09-16",

"2024-09-17",

"2024-09-18",

"2024-09-19",

"2024-09-20",

"2024-09-23"

"count": 10

{

"startDate": "2024-10-26",

"endDate": "2024-11-04",

"validDays": [

"2024-10-28",

"2024-10-29",

"2024-10-30",

"2024-11-01",

"2024-11-04"

"count": 5

{

"startDate": "2024-12-22",

"endDate": "2025-01-16",

"validDays": [

"2024-12-23",

"2024-12-26",

"2024-12-27",

"2024-12-30",

"2024-12-31",

"2025-01-02",

"2025-01-03",

"2025-01-06",

"2025-01-07",

"2025-01-08",

"2025-01-09",

"2025-01-10",

"2025-01-13",

"2025-01-14",

"2025-01-15",

"2025-01-16"

"count": 16

}

]

The getWorkdays() function

Parameters

This function takes three arguments:

workperiod: This is the array object containing the start date and end date for each of the sample periods.
weekends: The array of designated weekends as a number. For example, Saturday and Sunday would look like this: [0,6]
holidays: An array of holidays as date strings.

Function Variables and UTC dates

In this section, we’ll prepare our dates for efficient comparison by converting them into milliseconds so that we can extract our valid dates.

Calculating a Whole Day in Milliseconds (Line 16):

- - We start by calculating the duration of a whole day in milliseconds. This will allow us to iterate over each day and check if it is a valid date.

Handling Timezone and Daylight Saving (Lines 22-29):

- To avoid any timezone-related quirks or issues with daylight-saving events, we’ll convert our date periods to Coordinated Universal Time (UTC). We’ll create a small private method at the top of our function to handle this conversion.
- Note: We’ll encounter UTC conversions multiple times in our code, so having a dedicated method simplifies things.

Converting the Array of Holiday Dates to UTC Time (Line 32):

- Finally, we’ll convert our holiday array of dates to UTC time in milliseconds so that we can directly reference them against the currently iterated day.

Map an array of workdays

Next, we need to map our array of found workdays for each sample date range (Line 35). To do this we will use the JavaScirpt map method that takes a function as an argument.

Map Variables

We will need to iterate through each day within our date range checking if the date is not a weekend or a holiday and then record that date. To do this, we will need to set a few variables.

Get start and end days as UTC time in milliseconds (Lines 37-38)

Both the start date and end date will need to be converted to UTC dates using our private getUTC() method.

Set Mutable Variables ( Lines 41-42)

When we look at each date we need to check that day’s day of the week. To determine the starting day of the week of our range of dates we use the JavaScirpt getDay() method. This will update for each new day we check.
Next, we need to set our first day to check as a day.

Store Valid Wordays (Line 44)

Here we store our valid workdays in the validWorkday array.

Iterating over the days in the range

Our main task now is to iterate over each day in the range.

Not a Weekend or a Holiday (Line 49-52)

First, we check if the current day of the week is not included in the weekends array and the current day in UTC format does not exist in the holidayUTC array.
If both of these conditions are met, then the day is a valid workday and we add it to our validWorkay array.

Preparing for the Next Day (Lines 54-55)

Now we need to prepare for the next day check by:
- Updating the day by another day using the dayMilliseconds variable.
- Getting the next day of the week. Here, we need to keep in mind that if the next day of the week is equal to seven then it will go back to the start of the week which will be zero.

Returning it all back

Once we have our valid workday for the selected range, we return that array to generate our mapped array of workdays.

Finally, we return the workdays array back to the calling function.

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

That’s all there is to get a list of valid workdays in a range in JavaScript. I use this function often when providing analysis tools for clients who want to see aggregate workdays over a period of days.

I’d love to hear what you would use this function for.

~Yagi

Duplicate Filter Views in Selected Google Sheet Tabs with Google Apps Script

While there is not way to directly duplicate Filter Views into other sheet tabs in Google Sheets we can do this with a little bit of Google Apps Script magic.

In this tutorial, we will walk through how to duplicate all filter views from a source Google Sheets tab and duplicate them into selected sheets tab.

We’ll start off with an example sheet to better understand what we are going to achieve and then share the code along with a quick-use guide for those of you who want to to just get in and use the script in your own project.

Next we will provide a video tutorial walking through how I built the script and wrap everything up with some bonus scripts to extract different parts of the code. If you get to this stage you should have a better understanding on how to work with Filter Views programmatically.

Let’s dive in!

The Example Google Sheet

The best way of following how the code works is with an example.

In our example sheet we have 6 different stores in six different Google Sheets tabs. Each store contains the same headers; Date, Company, Name, Notes, Type/Specialty, Quoted, Actual.

We also have a NOTES sheet tab that provides instructions for the sheet.

If you want to play along, you can grab the example sheet from the link below:

Duplicate Filter View – Google Sheet Starter

Aim

We want to create a set of matching Filter Views for each of our stores without having to manually duplicate each one by hand in the sheet tab.

We have five filter views that we want to include in each of our company sheet tabs.

Of course, we don’t want to add filter views to our NOTES sheet tab.

The Problem

While we can create individual filter views inside a tab, we can’t migrate those filter views over to existing sheet tabs. So the only Google Sheets alternative is to manually update each sheet tab with our list of filter views that we want to add.

Another problem then arises when we need to make a modification to one or more of our filter views. All tabs have to be then modified by hand, increasing the chance of mistakes and significantly increase a big old case of boring.

Boring Boss Baby GIFfrom Boring GIFs

Imagine if you had 50 different sheet tabs for 50 different businesses. That would be a nightmare to create and update.

The Solution

We will only create and update filter views in our first business sheet tab that we have called ‘MAIN’ in our example. Then we will use some Google Apps Script to update all the other business sheet tabs with the filter views.

One thing that is important to keep in mind when the script is being built is to ensure that the filter view length changes for each sheet tab to accommodate the length of rows in each business sheet tab as they change.

If we need to make adjustments to our filter views and then update all business sheet tabs, we will first need to remove the existing filter views in all but the origin sheet tab that have the same name as the origin filter views (for us, “MAIN” tab views) before updating them. Otherwise we will generate an increasingly long list of filter views that all have the same view name.

Also, we probably don’t want other users to be able to ‘accidentally’ edit our Apps Script Code for the Google Sheet, so we will store the scrip unbound in a separate Apps Script file.

Here’s the code.

The Duplicate Filter Views Code

To keep things neat and tidy we will keep our duplicate filter view code in a new Google Script (*.gs) file. In our main Code.gs file I’ll add some sample code to add in all of our information needed to run the script.

Keep in mind that you could call the duplicate filter views script on its own like I have below or part of a larger process in your own project.

For your own project, you will need to create the duplicateFilterViews.gs file and copy and paste in the script. Optionally you can add the Code.gs file to run the script like I have or build your own.

Note! Unless your Google Sheets project is exclusively for you or a highly trusted team, I would recommend creating a separate Google Apps Script Project.

Code.gs

/**
 * Main run function to duplicate filter views into selected sheet tabs. 
 * This is a sample function that could be converted into a UI or be included
 * as part of a script process. 
 */
function runsies_duplicateFilterViews() {
  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet ID. Make sure you change this to your own Sheet ID if you are playing along.
  const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from. 
  const destinationTabList = {
    areExcludedTabs : true, // Is this a list of sheet tabs to exclude?
    tabNames : [
      "NOTES" // add all sheet tabs by name that you wish to include or exclude separated by a comma
    ]
  };

  duplicateFilterViews(ssID, sourceSheetName, destinationTabList)

};

/**

* Main run function to duplicate filter views into selected sheet tabs.

* This is a sample function that could be converted into a UI or be included

* as part of a script process.

function runsies_duplicateFilterViews() {

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet ID. Make sure you change this to your own Sheet ID if you are playing along.

const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from.

const destinationTabList = {

areExcludedTabs : true, // Is this a list of sheet tabs to exclude?

tabNames : [

"NOTES" // add all sheet tabs by name that you wish to include or exclude separated by a comma

]

};

duplicateFilterViews(ssID, sourceSheetName, destinationTabList)

};

Quick Use Guide

Add the Google Sheets Advance Service

You will need to access the Advanced Google Sheets Service for Apps Script before continuing. To do this select the Add a service plus button from the Services menu then scroll through the list of services until you find the Google Sheets serivce. Select it and click Add.

Setting up your reference data

The runsies_duplicateFilterView() function for our example contains all of the data we need to run our script.

We first list all of our variables:

ssID (string) – The Spreadsheet ID for the Google Sheet you are working on found in the URL of the document.
click to Expand!
sourceSheetName (string) – The name of the Google Sheet tab that contains the Filter Views that you want to duplicate. For our example this is the “MAIN” sheet tab.
destinationTabList (Object) – this Object contains two properties:
- areExcludedTabs (boolean) – Are you providing a list of all sheet tabs you want to exclude from duplicating the filter views? If so, mark true. This is probably the most common case. Otherwise mark it false if you are providing a list of all sheet tabs that you want to include. If so, mark false.
  We chose true in our example so we only have to add one item (excluding the ‘NOTES’ tab)…cause we lazy.
- tabNames (array) – An array of all the sheet tab included or excluded depending on your choice in areExcludedTabs. Don’t add the source sheet tab to this list.
  - E.g. of included list:["Sheet 2", "Sheet 3", "Sheet 4", "Sheet 5", "Sheet 6"]
  - E.g. of excluded list: ["Notes"]

Finally we run the duplicateFilterViews(ssID, sourceSheetName, destinationTabList). Note that we have included the three constant variables as our arguments for the function.

Paste in the duplicateFilterViews.gs code and run

After you have updated your Code.gs file and added the script to your newly created duplicateFilterView.gs file (script below). Save the file and select run from the menu bar.

Once the script has run, you can check our Google Sheet tabs to see that all filter views have been duplicated successfully.

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.

duplicateFilterViews.gs

Copy and paste the script below into your own project. I recommend adding it to a separate duplicateFilterViews.gs file for easier management.

/**
 * Object containing either a list of all sheet tables in which to copy the 
 * filter views or list or all sheet tables to be excluded from duplicating the list of filter views.
 * @typedef {Object} destTabList
 * @param {Boolean} destTabList.areExcludedTabs - True if tabNames is list of excluded tabs. False if tabNames is
 * list of tabs to add the filter views to.
 * @param {Array} destTabList.tabNames - Tab names of either excluded tabs or included tabs to contain filter views.
 */


/**
 * Duplicates the all of filter views of a selected sheet tab to selected sheet tabs in the same Spreadsheet.
 * 
 * @param {String} ssID - Spreadsheet ID
 * @param {String} sourceSheetName - Selected sheet tab to duplicate.
 * @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.
 */
function duplicateFilterViews(ssID, sourceSheetName, destTabList){

  // ########## VARIABLES ##########
  const ss = SpreadsheetApp.openById(ssID);
  const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

  // Get list of sheets by id minus selected sheet and exclusion list.
  const sheetIdList = getSheetIdList(ss, destTabList, sourceSheetName);

  const filterViews = getAllFilterViews(ssID);



  // Get only the filter views for the selected sheet.
  const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);


  removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);

  createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews);
  
};


/**
 * Gets a list of all the sheet tab IDs of all the sheet tabs that will have the filter views 
 * duplicated in. 
 * 
 * @param {SpreadsheetApp.Spreadsheet} ss 
 * @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.
 * @param {string} sourceSheetName - The name of the source sheet tab. 
 * @returns {array} Array of all selected sheet tab ids. 
 */
function getSheetIdList(ss, destTabList, sourceSheetName){
  return ss.getSheets().reduce((acc, sheet) => {

    // Check if we have an inclusion or exclusion list. 
    if(destTabList.areExcludedTabs){

      // Exclusion list
      if (![...destTabList.tabNames, sourceSheetName].includes(sheet.getName())) {
        return acc = acc.concat(sheet.getSheetId());
      };

    }else{
      
      // Inclusion list
      if ([...destTabList.tabNames].includes(sheet.getName())) {
        return acc = acc.concat(sheet.getSheetId());
      };
    };

    return acc
  }, []);
};


/**
 * Get an array of objects containing all filter views for all sheet tabs in a spreadsheet. 
 * 
 * @param {string} ssID - the id of the selected spreadsheet. 
 * @returns {Array<Object>} - Each array is a sheet tab of filter views.  
 */
function getAllFilterViews(ssID){
  // Get full list of filter views for the entire spreadsheet.
  const request = { fields: ["sheets/filterViews"] }
  const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;

  // Just the array items labelled 'filterView' in the Object
  return allFilterViews.filter(view => view.filterViews);
};



/**
 * Gets all the filter views for a selected sheet tab. 
 * 
 * @param {Array<Object>} filterViews - an array of objects containing all fiter views for all sheet tabs in a spreadsheet. 
 * @param {Number} SheetID - The ID number of the selected source sheet. 
 * @returns {Array<Object>} - Each array is a sheet tab of filter views.  
 */
function getSelectedSheetFilterViews(filterViews, sheetID){
  return filterViews.filter(view => {

    if (view.filterViews[0].range.sheetId == sheetID) {
      return view
    }
  })[0];

}

/**
 * Removes any existing copies of filter views in destination sheet tab locations that match the titles of the sheet
 * views in the source sheet tab. 
 * 
 * @param {string} ssID - the id of the selected spreadsheet. 
 * @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab. 
 * @param {Array<Object>} filterViews - an array of objects containing all filter views for all sheet tabs in a spreadsheet.
 * @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs. 
 */
function removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList){

  // Get a list of all the views by name in the source sheet tab.
  const listOfViewsByName = selectedSheetFilterViews.filterViews.map(view => view.title);

  // Iterate through list of views in all sheets to edit.
  let viewIdsToRemove = []


  filterViews.forEach(sheet => {
    if(sheet.hasOwnProperty("filterViews") && sheetIdList.includes(sheet.filterViews[0].range.sheetId)){
      sheet.filterViews.forEach(view => {

        if (listOfViewsByName.includes(view.title)) {

          const deleteView = {
            "deleteFilterView": {
              "filterId": view.filterViewId
            }
          }
          viewIdsToRemove.push(deleteView)
        }
      })
    }

  })


  const delResource = {
    requests: viewIdsToRemove
  };

  if(viewIdsToRemove.length != 0){
    Sheets.Spreadsheets.batchUpdate(delResource, ssID)
  };
};


/**
 * Create new filter views for each selected destination sheet tab based on selected
 * source sheet tab. 
 * 
 * @param {string} ssID - the id of the selected spreadsheet. 
 * @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs.
 * @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab. 
 */
function createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews){
  // Loop through every sheet tab that needs to be updated. 
  sheetIdList.forEach(sid => {
    const resource = {
      requests: []
    }

    // Loop through the selected filter view.
    selectedSheetFilterViews.filterViews.forEach(view => {

      // Add each view to the filter property.
      let addFilterView = { 'addFilterView': { filter: view } }

      // Remove the old filterViewId
      delete addFilterView.addFilterView.filter.filterViewId

      // Add the filter view to the new sheet ID.
      addFilterView.addFilterView.filter.range.sheetId = sid

      // Add the new last row to the filter view.
      addFilterView.addFilterView.filter.range.endRowIndex = getSheetById(ssID, sid).getLastRow()

      // Add the view to hte resource.requests array.
      resource.requests.push(addFilterView)

    })
    Sheets.Spreadsheets.batchUpdate(resource, ssID);
  })
};

/** 
  * Searches within a given Google Spreadsheet for a provided Sheet ID and returns
  * the Sheet if the sheet exists; otherwise it will return undefined if not found.
  *
  * @param {Spreadsheet} ss - a Google Spreadsheet object
  * @param {Integer} sheetId - the ID of a Google Sheet 
  * @return {Sheet} the Google Sheet object if found; otherwise undefined
  */
function getSheetById(ssID, sheetId) {
  const ss = SpreadsheetApp.openById(ssID)
  var foundSheets = ss.getSheets().filter(sheet => sheet.getSheetId() === sheetId);
  return foundSheets.length ? foundSheets[0] : undefined;
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

/**

* Object containing either a list of all sheet tables in which to copy the

* filter views or list or all sheet tables to be excluded from duplicating the list of filter views.

* @typedef {Object} destTabList

* @param {Boolean} destTabList.areExcludedTabs - True if tabNames is list of excluded tabs. False if tabNames is

* list of tabs to add the filter views to.

* @param {Array} destTabList.tabNames - Tab names of either excluded tabs or included tabs to contain filter views.

/**

* Duplicates the all of filter views of a selected sheet tab to selected sheet tabs in the same Spreadsheet.

* @param {String} ssID - Spreadsheet ID

* @param {String} sourceSheetName - Selected sheet tab to duplicate.

* @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.

function duplicateFilterViews(ssID, sourceSheetName, destTabList){

// ########## VARIABLES ##########

const ss = SpreadsheetApp.openById(ssID);

const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

// Get list of sheets by id minus selected sheet and exclusion list.

const sheetIdList = getSheetIdList(ss, destTabList, sourceSheetName);

const filterViews = getAllFilterViews(ssID);

// Get only the filter views for the selected sheet.

const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);

createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews);

};

/**

* Gets a list of all the sheet tab IDs of all the sheet tabs that will have the filter views

* duplicated in.

* @param {SpreadsheetApp.Spreadsheet} ss

* @param {destTabList} destTabList - Destination tab list and option to contain inclusion or exclusion.

* @param {string} sourceSheetName - The name of the source sheet tab.

* @returns {array} Array of all selected sheet tab ids.

function getSheetIdList(ss, destTabList, sourceSheetName){

return ss.getSheets().reduce((acc, sheet) => {

// Check if we have an inclusion or exclusion list.

if(destTabList.areExcludedTabs){

// Exclusion list

if (![...destTabList.tabNames, sourceSheetName].includes(sheet.getName())) {

return acc = acc.concat(sheet.getSheetId());

};

}else{

// Inclusion list

if ([...destTabList.tabNames].includes(sheet.getName())) {

return acc = acc.concat(sheet.getSheetId());

};

return acc

}, []);

};

/**

* Get an array of objects containing all filter views for all sheet tabs in a spreadsheet.

* @param {string} ssID - the id of the selected spreadsheet.

* @returns {Array<Object>} - Each array is a sheet tab of filter views.

function getAllFilterViews(ssID){

// Get full list of filter views for the entire spreadsheet.

const request = { fields: ["sheets/filterViews"] }

const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;

// Just the array items labelled 'filterView' in the Object

return allFilterViews.filter(view => view.filterViews);

};

/**

* Gets all the filter views for a selected sheet tab.

* @param {Array<Object>} filterViews - an array of objects containing all fiter views for all sheet tabs in a spreadsheet.

* @param {Number} SheetID - The ID number of the selected source sheet.

* @returns {Array<Object>} - Each array is a sheet tab of filter views.

function getSelectedSheetFilterViews(filterViews, sheetID){

return filterViews.filter(view => {

if (view.filterViews[0].range.sheetId == sheetID) {

return view

}

})[0];

}

/**

* Removes any existing copies of filter views in destination sheet tab locations that match the titles of the sheet

* views in the source sheet tab.

* @param {string} ssID - the id of the selected spreadsheet.

* @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab.

* @param {Array<Object>} filterViews - an array of objects containing all filter views for all sheet tabs in a spreadsheet.

* @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs.

function removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList){

// Get a list of all the views by name in the source sheet tab.

const listOfViewsByName = selectedSheetFilterViews.filterViews.map(view => view.title);

// Iterate through list of views in all sheets to edit.

let viewIdsToRemove = []

filterViews.forEach(sheet => {

if(sheet.hasOwnProperty("filterViews") && sheetIdList.includes(sheet.filterViews[0].range.sheetId)){

sheet.filterViews.forEach(view => {

if (listOfViewsByName.includes(view.title)) {

const deleteView = {

"deleteFilterView": {

"filterId": view.filterViewId

}

viewIdsToRemove.push(deleteView)

}

})

}

})

const delResource = {

requests: viewIdsToRemove

};

if(viewIdsToRemove.length != 0){

Sheets.Spreadsheets.batchUpdate(delResource, ssID)

};

/**

* Create new filter views for each selected destination sheet tab based on selected

* source sheet tab.

* @param {string} ssID - the id of the selected spreadsheet.

* @param {Array} sheetIdList - Sheet IDs of selected destintation sheet tabs.

* @param {Object} selectedSheetFilterViews - all the filter views for a selected sheet tab.

function createNewFilterViews(ssID, sheetIdList, selectedSheetFilterViews){

// Loop through every sheet tab that needs to be updated.

sheetIdList.forEach(sid => {

const resource = {

requests: []

}

// Loop through the selected filter view.

selectedSheetFilterViews.filterViews.forEach(view => {

// Add each view to the filter property.

let addFilterView = { 'addFilterView': { filter: view } }

// Remove the old filterViewId

delete addFilterView.addFilterView.filter.filterViewId

// Add the filter view to the new sheet ID.

addFilterView.addFilterView.filter.range.sheetId = sid

// Add the new last row to the filter view.

addFilterView.addFilterView.filter.range.endRowIndex = getSheetById(ssID, sid).getLastRow()

// Add the view to hte resource.requests array.

resource.requests.push(addFilterView)

})

Sheets.Spreadsheets.batchUpdate(resource, ssID);

})

};

/**

* Searches within a given Google Spreadsheet for a provided Sheet ID and returns

* the Sheet if the sheet exists; otherwise it will return undefined if not found.

* @param {Spreadsheet} ss - a Google Spreadsheet object

* @param {Integer} sheetId - the ID of a Google Sheet

* @return {Sheet} the Google Sheet object if found; otherwise undefined

function getSheetById(ssID, sheetId) {

const ss = SpreadsheetApp.openById(ssID)

var foundSheets = ss.getSheets().filter(sheet => sheet.getSheetId() === sheetId);

return foundSheets.length ? foundSheets[0] : undefined;

}

Using the Google Sheets Advanced Service for Filter Views

We need to approach the Google Sheets Advance Service API quite differently to how we use the SpreadsheetApp class. The advance service requires us to retrieve and update an object or array-object to carry out our processes.

This means that we need to test the object directory path so we can see where locations are for us to find or update what we need. I’ve left this testing phase out of this tutorial, but this is something you will need to do.

For our project we need to use the Sheet.Spreadsheet resource. From here we will either get our Filter View data for the spreadsheet or send a batch update to make bulk changes to the sheet.

Get our Filter View Data

We retrieve our list of all filter views only one time in our getAllFilterViews() function.

Now we can simply get a list of all the date in the entire spreadsheet by using the get method and apply the selected spreadsheet ID like this:

Sheets.Spreadsheets.get(ssID)

Get just filter view field data

However, this is pretty wasteful and generates a bulky data set. Instead we can narrow in on the spreadsheets list of filter views by adding some some optional arguments to our get request.

Here, we use the “fields” property to tell the API what fields that we only want to retrieve. For us, this is our filter views. Our filter views are applied to each of the sheets in our spreadsheet so we must create the path “sheets/filterViews”. Our code then looks like this:

...
  // Get full list of filter views for the entire spreadsheet.
  const request = { fields: ["sheets/filterViews"] }
  const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;
...

...

// Get full list of filter views for the entire spreadsheet.

const request = { fields: ["sheets/filterViews"] }

const allFilterViews = Sheets.Spreadsheets.get(ssID, request).sheets;

...

This will return our data containing all the filter views.

Once retrieved we can follow the property tree or iterate through the sheets, and filter view arrays. In the code above we immediately reduce our data down to just the array of all of our sheets.

Batch update our Filter View Data

We update our filter view data on two occasions in our script. First when we delete all the duplicates in our destination sheet tabs and then to add our new duplicate filter data to our destination sheet tabs.

To do this we use the batchUpdate method. This method takes two parameters:

The resource containing the requests from us to update the filter views on the spreadsheet.
1. requests – this is our list of requests to update our data. It will contain an array of objects where each object contains the data that we wish to update.
  1. Instruction – Each update object starts off with an instruction that is known as a ‘request’. You can find a list of all available requests here.
The spreadsheet ID.

Delete a filter view

To delete a filter view we use the deleteFilterView request. This requests is pretty simple all it requires is for us to provide the filter id of the item we want to delete. You can see it in use in lines 133-136 of the DuplicateFilterView.gs file code above.

...

          const deleteView = {
            "deleteFilterView": {
              "filterId": view.filterViewId
            }
          }
...

...

const deleteView = {

"deleteFilterView": {

"filterId": view.filterViewId

}

...

Add a Filter View

Adding a filter view is much more complicated. Well… it would be if we were not just using an existing filter view and making a few modifications to it.

To add a filter view we use the the addFilterView request. This requests sets the filter and then an object containing all the data we need to build the object.

{ 'addFilterView': { filter: Object of filter view data } }

You can see how we added this on line 175 of our duplicateFilterViews.gs file.

For our tutorial we didn’t need to build the view in its entirety, we just needed to:

remove the existing id of the view (You shouldn’t have one, because it will be added for you)
update the range > sheet ID to the new destination id
update the end row to match the current depth of the data in the destination sheet

Lines 177-187 of DuplicateFilterView.gs

The Video Tutorial

Here’s the link to the Starter Sheet for you to follow along:

Duplicate Filter View – Google Sheet Starter

https://youtu.be/I5cWzmjXBM4

Duplicating Google Sheets filter views in other Sheet Tabs with Google Apps Script (https://youtu.be/I5cWzmjXBM4)

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

Bonus Functions

Maybe you only want to get a list of all of your filter views in a spreadsheet or all the filter views for a selected sheet tab. Perhaps you just want to delete all the non-source tab filter views you created.

Check out the following three functions to give you some ideas on how you might use the duplicateFilterViews.gs file to achieve this.

I have appended each function name with runsies_ but you can rename and rebuild them how you want.

If you have been playing along, you can add them to the Code.gs file to run them.

runsies_showAllFilterViews()

This function takes your spreadsheet ID and runs the showAllFilterViews() function. The function returns a full list of all the filter views in all of your sheet tabs for your selected Google Sheet spreadsheet.

/**
 * Gets a list of all filter views in the spreadsheet. 
 */
function runsies_showAllFilterViews(){
  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet. 

  const filterViews = getAllFilterViews(ssID);

  // Logging the results here you can use the filterViews variable how you like. 
  console.log(JSON.stringify(filterViews, null, " "));
};

/**

* Gets a list of all filter views in the spreadsheet.

function runsies_showAllFilterViews(){

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet.

const filterViews = getAllFilterViews(ssID);

// Logging the results here you can use the filterViews variable how you like.

console.log(JSON.stringify(filterViews, null, " "));

};

We then log the results, but you may wish to use them in other ways.

Keep in mind that the results will most likely be larger than what the console will contain, but logging the result will give you a good idea on how the Array of filter view objects for each tab is stored.

runsies_selectedSheetFilterViews()

This function gets all the filter views from all sheets and then returns just the data for the selected source sheet. In our case, this in ‘MAIN’, but you can change this to what ever sheet tab you are looking for.

/**
 * Gets all of the filter views contained in the selected sheet tab.
 */
function runsies_selectedSheetFilterViews(){

  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet. 
  const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from. 

  const ss = SpreadsheetApp.openById(ssID);
  const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

  const filterViews = getAllFilterViews(ssID);
  const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

  // Logging the results here you can use the filterViews variable how you like. 
  console.log(JSON.stringify(selectedSheetFilterViews, null, " "));
};

/**

* Gets all of the filter views contained in the selected sheet tab.

function runsies_selectedSheetFilterViews(){

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet.

const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from.

const ss = SpreadsheetApp.openById(ssID);

const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

const filterViews = getAllFilterViews(ssID);

const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

// Logging the results here you can use the filterViews variable how you like.

console.log(JSON.stringify(selectedSheetFilterViews, null, " "));

};

The results are then logged. The filterViews property will give you an array of all of the views in the selected tab.

runsies_deleteAllCopiedFilterViews()

If you no longer wish to have the duplicates of the source filter views in your destination sheet tabs, you can use this function to remove them.

You should put in all the same arguments in this function that you had for your runsies_duplicateFilterViews() function.

/**
 * Deletes all the filtered views in the destination locations based on the source sheet tabs list of 
 * filter views. Does not remove the source sheets list of filter views. 
 */
function runsies_deleteAllCopiedFilterViews(){

  const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet id. 
  const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from. 
  const destinationTabList = {
    areExcludedTabs : true,
    tabNames : [
      "NOTES"
    ]
  };

  const ss = SpreadsheetApp.openById(ssID);
  const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

  const filterViews = getAllFilterViews(ssID);
  const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

  const sheetIdList = getSheetIdList(ss, destinationTabList, sourceSheetName);
  removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);  
};

/**

* Deletes all the filtered views in the destination locations based on the source sheet tabs list of

* filter views. Does not remove the source sheets list of filter views.

function runsies_deleteAllCopiedFilterViews(){

const ssID = "1zj9k-MbZNZs2UOStcg4fp1tgqKp3dHh07Shv6pmLz5Y" // Your spreadsheet id.

const sourceSheetName = "MAIN" // The sheet tab that you want to get the filter views from.

const destinationTabList = {

areExcludedTabs : true,

tabNames : [

"NOTES"

]

};

const ss = SpreadsheetApp.openById(ssID);

const sheetID = ss.getSheetByName(sourceSheetName).getSheetId();

const filterViews = getAllFilterViews(ssID);

const selectedSheetFilterViews = getSelectedSheetFilterViews(filterViews, sheetID);

const sheetIdList = getSheetIdList(ss, destinationTabList, sourceSheetName);

removeExistingCopiesOfFilterViews(ssID, selectedSheetFilterViews, filterViews, sheetIdList);

};

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

Happy Coding!

~Yagi

Get a Unique List of Objects in an Array of Object in JavaScript

Recently, I needed a way to ensure that my JavaScript array of objects does not contain any duplicate objects based on an ‘id’ key. While I do enjoy more common approaches to this solution with the use of a simple ‘for’ loop it is always a bit of fun to see how folks have come up with modern solutions.

It wasn’t too long before I stumbled across this ES6 one-liner:

let uniqueObjArray = [...new Map(objArray.map((item) => [item["id"], item])).values()];

1	let uniqueObjArray = [...new Map(objArray.map((item) => [item["id"], item])).values()];

Pretty neat, huh?

Here are two locations I found them:

But as many code oneliners are, they are a little tricky to understand. So I wanted to spend some time understanding how this worked and thought, you might find this a little interesting too.

Let’s take a look at an example and then work through each bit as we go.

https://youtu.be/10CehsAWk7E

Get a Unique List of Objects in an Array of Object in JavaScript (https://youtu.be/10CehsAWk7E)

The Example

const myObjArray = [
    {
        name: "Eva Devore",
        character: "Evandra",
        episodes: 15,
    },
    {
        name: "Alessia Medina",
        character: "Nixie",
        episodes: 15,
    },
    {
        name: "Kendall Drury",
        character: "DM",
        episodes: 15,
    },
    {
        name: "Thomas Taufan",
        character: "Antrius",
        episodes: 14,
    },
    {
        name: "Alessia Medina",
        character: "Nixie",
        episodes: 15,
    },
];

// Creates an array of objects with unique "name" property values.
let uniqueObjArray = [
    ...new Map(myObjArray.map((item) => [item["name"], item])).values(),
];

console.log("uniqueObjArray", uniqueObjArray);

// LOGS:
// uniqueObjArray [
//   { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// ]

const myObjArray = [

{

character: "Evandra",

episodes: 15,

{

character: "Nixie",

episodes: 15,

{

character: "DM",

episodes: 15,

{

character: "Antrius",

episodes: 14,

{

character: "Nixie",

episodes: 15,

];

// Creates an array of objects with unique "name" property values.

let uniqueObjArray = [

...new Map(myObjArray.map((item) => [item["name"], item])).values(),

];

console.log("uniqueObjArray", uniqueObjArray);

// LOGS:

// uniqueObjArray [

// { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// ]

In the example above, we want to create a unique array of objects based on the ‘name’ property of each object set.

You can see that there is a duplicate property in positions 1 and 4 with key ‘name’ and value, ‘Alessia Medina’ that we need to remove.

You can also change the key to either the ‘character’ or ‘episodes’ property.

When the distinct array of objects is created it will set the last duplicate object as the object value. Why? Because the script will essentially reassign the ‘name’ property each time it loops through the array when creating the new map.

Let’s start breaking down the script so we can see how each one operates.

Code Breakdown

myObjArray.map

// Creates a new 2d array with the selected key in position 0 and the object in position 1 of each iteration.
let test_uniqueObjArray_map = myObjArray.map((item) => {
    return [item["name"], item];
});

// LOGS: test_uniqueObjArray_map
// [
//     ["Eva Devore", { name: "Eva Devore", character: "Evandra", episodes: 15 }],
//     ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }],
//     ["Kendall Drury", { name: "Kendall Drury", character: "DM", episodes: 15 }],
//     ["Thomas Taufan", { name: "Thomas Taufan", character: "Antrius", episodes: 14 }],
//     ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }]
// ];

// Creates a new 2d array with the selected key in position 0 and the object in position 1 of each iteration.

let test_uniqueObjArray_map = myObjArray.map((item) => {

return [item["name"], item];

});

// LOGS: test_uniqueObjArray_map

// [

// ["Eva Devore", { name: "Eva Devore", character: "Evandra", episodes: 15 }],

// ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }],

// ["Kendall Drury", { name: "Kendall Drury", character: "DM", episodes: 15 }],

// ["Thomas Taufan", { name: "Thomas Taufan", character: "Antrius", episodes: 14 }],

// ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }]

// ];

The first task of this script remaps the array of objects using the JavaScript map() method. This method takes a function, which in our is an arrow function.

Map method arrow functions generally look like this:

myArray.map((element) => <em>return new element as a result of a callback</em>)

1	myArray.map((element) => <em>return new element as a result of a callback</em>)

As an ordinary function, it would look like this:

myArray.map(function(element){
  return <em>new element as a result of a callback</em>
})

myArray.map(function(element){

return <em>new element as a result of a callback</em>

})

In our example above, we have our callback arguments on a new line so we will also need to include curly braces {}.

With the map method, the function will act on each array and return the result to generate a new array of the same length.

For us, our call back condition rebuilds each array to make a sub-array containing the value of each name key in the array as the zeroeth element and the object at the first element.

...

[item["name"], item];

...

...

[item["name"], item];

...

So the first element in the new array will look like this:

...

["Eva Devore", { name: "Eva Devore", character: "Evandra", episodes: 15 }]

...

...

["Eva Devore", { name: "Eva Devore", character: "Evandra", episodes: 15 }]

...

new Map

A quick side example

Before we continue, let’s take a quick look at a basic Map process on a 2d array:

// Creates a key-value map of a 2d array
let valuesObject = new Map([
    ["key_one", "val_one"],
    ["key_two", "val_two"],
    ["key_three", "val_three"],
]);
console.log("valuesObject", valuesObject);

// LOGS: valuesObject Map {
//   'key_one' => 'val_one',
//   'key_two' => 'val_two',
//   'key_three' => 'val_three'
// }

// Creates a key-value map of a 2d array

let valuesObject = new Map([

["key_one", "val_one"],

["key_two", "val_two"],

["key_three", "val_three"],

]);

console.log("valuesObject", valuesObject);

// LOGS: valuesObject Map {

// 'key_one' => 'val_one',

// 'key_two' => 'val_two',

// 'key_three' => 'val_three'

// }

To be frank, I didn’t really understand the Map object too well until I explored this script.

Map object stores key-value pairs similar to an Object. However, the Map maintains the insertion order of the properties. You’ll see Map objects often displayed like this when logged out in the console.

'key' => 'value'

1	'key' => 'value'

Map can be iterated through in a similar way to a 2d array with the zeroeth element as a key and the next element as a value for each property of the map – ['key', 'value'].

Alternatively, we can also generate a Map from a 2d array as we did in the example above – turning each sub-array into a key-value pair.

Back to our main example…

new Map of our example

// PREVIOUS LOG: test_uniqueObjArray_map
// [
//     ["Eva Devore", { name: "Eva Devore", character: "Evandra", episodes: 15 }],
//     ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }],
//     ["Kendall Drury", { name: "Kendall Drury", character: "DM", episodes: 15 }],
//     ["Thomas Taufan", { name: "Thomas Taufan", character: "Antrius", episodes: 14 }],
//     ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }]
// ];

// Create a new Map from our mapped data. 
let test_uniqueObjArray_NewMap = new Map(test_uniqueObjArray_map);

// LOGS: test_uniqueObjArray_NewMap Map {
//   'Eva Devore' => { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   'Alessia Medina' => { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   'Kendall Drury' => { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   'Thomas Taufan' => { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// }

// PREVIOUS LOG: test_uniqueObjArray_map

// [

// ["Eva Devore", { name: "Eva Devore", character: "Evandra", episodes: 15 }],

// ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }],

// ["Kendall Drury", { name: "Kendall Drury", character: "DM", episodes: 15 }],

// ["Thomas Taufan", { name: "Thomas Taufan", character: "Antrius", episodes: 14 }],

// ["Alessia Medina",{ name: "Alessia Medina", character: "Nixie", episodes: 15 }]

// ];

// Create a new Map from our mapped data.

let test_uniqueObjArray_NewMap = new Map(test_uniqueObjArray_map);

// LOGS: test_uniqueObjArray_NewMap Map {

// 'Eva Devore' => { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// 'Alessia Medina' => { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// 'Kendall Drury' => { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// 'Thomas Taufan' => { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// }

We are using the data we retrieved from our previous example here to remove some of the clutter from the process. I have added those results at the top of the code block above.

In this example, we simply apply new Map to this array of data. By doing this Map turns into a type of Object with a key-value pair. Now keep in mind that Object keys are the highlander of data types – there can be only one.

Rucoy Highlander GIF from Rucoy GIFs

What does this mean beyond a bad joke that really shows my age?

It means that each key must be unique. All of our keys are now the names of our users. The new Map constructor process will then iterate through each name and store it and then assign its value. If a key already exists it will overwrite it with this next value with the same key name.

This means that the last duplicate key will always be displayed. Effectively only storing unique values.

Displaying the keys of each property in the Map

We can generate iterators to go through each key or value with the keys() and values() methods respectively.

We will have a look at the keys() method first quickly.

Let’s apply keys() to our test_uniqueObjArray_NewMap Map we generated above.

// PREVIOUS LOG: test_uniqueObjArray_NewMap Map {
//   'Eva Devore' => { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   'Alessia Medina' => { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   'Kendall Drury' => { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   'Thomas Taufan' => { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// }

console.log("test_uniqueObjArray_NewMap_keys", test_uniqueObjArray_NewMap_keys);

// LOGS: test_uniqueObjArray_NewMap_keys [Map Iterator] {
//   'Eva Devore',
//   'Alessia Medina',
//   'Kendall Drury',
//   'Thomas Taufan'
// }

// PREVIOUS LOG: test_uniqueObjArray_NewMap Map {

// 'Eva Devore' => { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// 'Alessia Medina' => { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// 'Kendall Drury' => { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// 'Thomas Taufan' => { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// }

console.log("test_uniqueObjArray_NewMap_keys", test_uniqueObjArray_NewMap_keys);

// LOGS: test_uniqueObjArray_NewMap_keys [Map Iterator] {

// 'Eva Devore',

// 'Alessia Medina',

// 'Kendall Drury',

// 'Thomas Taufan'

// }

As you can see this produces an iterator of all the (unique) keys in our data as a Map Iterator. It’s not quite an array of objects, but it allows us to iterate over each key to do something with it.

The same is true for the values() method.

Displaying the values of each property in the Map

// PREVIOUS LOG: test_uniqueObjArray_NewMap Map {
//   'Eva Devore' => { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   'Alessia Medina' => { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   'Kendall Drury' => { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   'Thomas Taufan' => { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// }

let test_uniqueObjArray_NewMap_values = test_uniqueObjArray_NewMap.values();

console.log(
    "test_uniqueObjArray_NewMap_values",
    test_uniqueObjArray_NewMap_values
);


// LOGS: test_uniqueObjArray_NewMap_values [Map Iterator] {
//   { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// }

// PREVIOUS LOG: test_uniqueObjArray_NewMap Map {

// 'Eva Devore' => { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// 'Alessia Medina' => { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// 'Kendall Drury' => { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// 'Thomas Taufan' => { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// }

let test_uniqueObjArray_NewMap_values = test_uniqueObjArray_NewMap.values();

console.log(

"test_uniqueObjArray_NewMap_values",

test_uniqueObjArray_NewMap_values

);

// LOGS: test_uniqueObjArray_NewMap_values [Map Iterator] {

// { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// }

Here we want to get an iterator of our values so that we can recreate an array of objects again.

Using the values() iterator method we now have our Map values ready to go.

Using the spread syntax to create our array of object

Now that we have an iterator of our unique values we can now place them in our spread syntax – “...“.

When you apply the spread syntax on an array, it will add each item of an iterable to the array. Take a look at what it does to our Map values.

// PREVIOUS LOG: test_uniqueObjArray_NewMap_values [Map Iterator] {
//   { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// }

let test_uniqueObjArray_NewMap_values_asArray = [
    ...test_uniqueObjArray_NewMap_values,
];

// LOGS: test_uniqueObjArray_NewMap_values_asArray [
//   { name: 'Eva Devore', character: 'Evandra', episodes: 15 },
//   { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },
//   { name: 'Kendall Drury', character: 'DM', episodes: 15 },
//   { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }
// ]

// PREVIOUS LOG: test_uniqueObjArray_NewMap_values [Map Iterator] {

// { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// }

let test_uniqueObjArray_NewMap_values_asArray = [

...test_uniqueObjArray_NewMap_values,

];

// LOGS: test_uniqueObjArray_NewMap_values_asArray [

// { name: 'Eva Devore', character: 'Evandra', episodes: 15 },

// { name: 'Alessia Medina', character: 'Nixie', episodes: 15 },

// { name: 'Kendall Drury', character: 'DM', episodes: 15 },

// { name: 'Thomas Taufan', character: 'Antrius', episodes: 14 }

// ]

This is similar to using the Array.from() static method that would look like this:

let test_uniqueObjArray_NewMap_values_asArray = Array.from(
    test_uniqueObjArray_NewMap_values
);

let test_uniqueObjArray_NewMap_values_asArray = Array.from(

test_uniqueObjArray_NewMap_values

);

Performance

So how does this one-liner stack up against a more traditional for-loop like this?

var array = [
    {
        name: "Eva Devore",
        character: "Evandra",
        episodes: 15,
    },
    {
        name: "Alessia Medina",
        character: "Nixie",
        episodes: 15,
    },
    {
        name: "Kendall Drury",
        character: "DM",
        episodes: 15,
    },
    {
        name: "Thomas Taufan",
        character: "Antrius",
        episodes: 14,
    },
    {
        name: "Alessia Medina",
        character: "Nixie",
        episodes: 15,
    },
];;
var unique = [];
var distinct = [];
for( let i = 0; i < array.length; i++ ){
  if( !unique[array[i].name]){
    distinct.push(array[i].name);
    unique[array[i].name] = 1;
  }
}

console.log(distinct)

var array = [

{

character: "Evandra",

episodes: 15,

{

character: "Nixie",

episodes: 15,

{

character: "DM",

episodes: 15,

{

character: "Antrius",

episodes: 14,

{

character: "Nixie",

episodes: 15,

];;

var unique = [];

var distinct = [];

for( let i = 0; i < array.length; i++ ){

if( !unique[array[i].name]){

distinct.push(array[i].name);

unique[array[i].name] = 1;

}

console.log(distinct)

Surprisingly better than I thought it would, to be honest.

Running a benchmark test with jsbench.me, the one-liner ran only 13.74% slower. Which is pretty good compared to some of the other options I found out there.

Conclusion

So should you be using this oneliner over the for loop? Is an impressive one-liner better than something more clear? To be honest, I am on the fence.

I do like the way this script operates. It is clean and once I got my head around the Map object, it did make a lot of sense. I think if I saw something like this in the wild I could pretty easily identify what it was for and see that it was a nice short solution to a problem.

I don’t think I would use this approach when I need to iterate over objects in the many thousands. Then speed becomes important. But if I need something in my toolkit to solve a problem like this, then I am definitely going to use it.

I have an example of how I used the code to check for duplicate selections of files in Google Drive here:

https://yagisanatode.com/2021/06/27/creates-a-google-workspace-add-on-file-picker-card-with-cardservice-that-opens-a-google-picker-in-an-overlay-window-google-apps-script/

What do you think? Is it too abstract or is it elegant?

Did this tutorial help you understand the JavaScript one-liner better? Do you think you would apply it in your own projects?

Please let me know your thoughts in the comments below. I really enjoy hearing how things are used in the wild.

Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)

Google Apps Script: SpreasheetApp, DocumentApp, DriveApp; Google Sheets, Google Docs

If you have ever worked in LibreOffice or Microsoft Excel you will probably be familiar with the mail merge. Traditionally, mail merge is used to create multiple versions of a document and snail-mail them to someone.

These days, we don’t often use the snail mail approach, but it is a regular occurrence for us to need to produce multiple versions of reports based on a data set usually from a spreadsheet.

In this tutorial, we will create a document merger that will create new Google Documents based on a dataset from a Google Sheet using Google Apps Script.

If you want to quickly jump into your own project with our script, I’ll provide you with a quick-use guide.

Then, we will set up a template for our Google Doc and generate our Google Sheet data (don’t worry, I’ll share the document so you can follow along).

Finally, we will jump into the breakdown of the code for those legends who are learning how to create their own Google Apps Script.

Let’s get started:

Note: As always, take what you need and don’t worry about the rest.

Continue reading “Google Apps Script: Create multiple versions of a document based on Google Sheet Data and a Google Doc Template (Mail Merge)”

Google Apps Script: Add and removed Google Sheets columns based on a search array

Google Apps Script: V8 engine, map, filter, reduce, includes, 2d arrays, matrix

Have you ever wanted to delete or add columns in a Google Sheet, based on another set of Sheet data?

I know I have.

There have been a number of instances where I wanted to insert new columns or removed unused columns in large Google Sheets projects with Google Apps Script.

In the past, I have dynamically set headers based on something like an IMPORTRANGE or FILTER, or simply from an array ({}) from another sheet tab or Google Sheet. However, when I update the original data the headers change but all the data underneath them does not move along to the updated column with it.

Now all my data is not lined up with the header! As you can imagine, this creates some serious problems.

In this tutorial, we’ll show you how to use Google Apps Script to update your headers based on another sheets values. These sheets values can come from the current Google Sheet workbook or another one. We will also ensure that the data below the headers is migrated along with the new header location.

Take a look at the visual example below:

As always, read as much or as little as you need to get the job done or learn the skill.

Continue reading “Google Apps Script: Add and removed Google Sheets columns based on a search array”

The Example

The Video Tutorial

The Code

The runsies() function

The getWorkdays() function

Parameters

Function Variables and UTC dates

Map an array of workdays

Map Variables

Iterating over the days in the range

Returning it all back

Share this:

Like this:

The Example Google Sheet

Aim

The Problem

The Solution

The Duplicate Filter Views Code

Code.gs

Quick Use Guide

Add the Google Sheets Advance Service

Setting up your reference data

Paste in the duplicateFilterViews.gs code and run

duplicateFilterViews.gs

Using the Google Sheets Advanced Service for Filter Views

Get our Filter View Data

Get just filter view field data

Batch update our Filter View Data

Delete a filter view

Add a Filter View

The Video Tutorial

Bonus Functions

runsies_showAllFilterViews()

runsies_selectedSheetFilterViews()

runsies_deleteAllCopiedFilterViews()

Share this:

Like this:

The Example

Code Breakdown

myObjArray.map

new Map

A quick side example

new Map of our example

Displaying the keys of each property in the Map

Displaying the values of each property in the Map

Using the spread syntax to create our array of object

Performance

Conclusion

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: