Get the Difference Between Two Arrays in JavaScript

Sometimes we want to compare the difference between two arrays in JavaScript and return the remaining values in a new array.

This can be useful for inventory or purchase management where we could compare the selected items against the available stock, for example.

In this tutorial, we will cover two approaches to this:

A single set of unique items in an array.
A main array containing duplicate items.

Let’s get cracking!

Continue reading “Get the Difference Between Two Arrays in JavaScript”

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”

Why Object.create() doesn’t work in Google Apps Script and how to fix it.

If you have found this post while searching to try to figure out why your JavaScript Object.create() method ~~is being a big stupid head~~ isn’t working how you would expect it would in Google Apps Script, you have come to the right place.

You’ve probably used Object.create() in a JavaScript project in the past to create a new object based on a template object and then added another property or so to it.

  const a = {name:"Yagi", species:"goat"}

  //In Javascript it works!
  const newObj = Object.create(a);
  newObj.foodAvailable = 100
  
  console.log("a: ", a);
  //result >> "a: ", {
  //name: "Yagi",
  //species: "goat"
  //}
  
  
  console.log("newObj: ", newObj);
  //result>> "newObj: ", {
  //foodAvailable: 100,
  //name: "Yagi",
  //species: "goat"
  //}

const a = {name:"Yagi", species:"goat"}

//In Javascript it works!

const newObj = Object.create(a);

newObj.foodAvailable = 100

console.log("a: ", a);

//result >> "a: ", {

//name: "Yagi",

//species: "goat"

//}

console.log("newObj: ", newObj);

//result>> "newObj: ", {

//foodAvailable: 100,

//name: "Yagi",

//species: "goat"

//}

As you can see in the example above, it works just dandy in JavaScript. The original object "a" is not affected if we add a new property to the "newObj" object.

However, if you try and do the same thing in Google Apps Script, as you have no doubt discovered, you will get the following results:

function appsScriptWhyHaveYouForsakenMe(){
  const a = {name:"Yagi", species:"goat"}

  //Error
  const newObj = Object.create(a);
  newObj.foodAvailable = 100
  
  console.log("a: ", a);
  //a:  { name: 'Yagi', species: 'goat' }
  
  console.log("newObj: ", newObj);
  // newObj:  { foodAvailable: 100 }
}

function appsScriptWhyHaveYouForsakenMe(){

const a = {name:"Yagi", species:"goat"}

//Error

const newObj = Object.create(a);

newObj.foodAvailable = 100

console.log("a: ", a);

//a: { name: 'Yagi', species: 'goat' }

console.log("newObj: ", newObj);

// newObj: { foodAvailable: 100 }

}

So what’s going on? What can I do to fix this? Read on or select from the menu to get to the bits you are interested in.

Some Solutions

Spread Syntax to the rescue

With the modernisation of Google Apps Script with their introduction of the V8 runtime back in February of 2020, we can now use the JavaScript Spread Syntax ({...obj})to create a shallow clone of the original object (a.k.a object literal).

This is my favourite approach. It is quite neat and elegant, but perhaps not as explicit in its meaning for someone reviewing your code.

If you create a new object and add a property to it

Dam it! Now I’ve got that Beyonce song in my head. ✔💍*

Let’s fix the original code with our spread syntax.

function ohohohhh(){
  const a = {name:"Yagi", species:"goat"};

  //Success
  const newObj = {...a};
  newObj.foodAvailable = 100;

  console.log("a: ", a);
  //    a:  { name: 'Yagi', species: 'goat' }

  console.log("newObj: ", newObj);
  // newObj:  { name: 'Yagi', species: 'goat', foodAvailable: 100 }
};

function ohohohhh(){

const a = {name:"Yagi", species:"goat"};

//Success

const newObj = {...a};

newObj.foodAvailable = 100;

console.log("a: ", a);

// a: { name: 'Yagi', species: 'goat' }

console.log("newObj: ", newObj);

// newObj: { name: 'Yagi', species: 'goat', foodAvailable: 100 }

};

You can see on line 5 we have a nice neat spread syntax to add to your "newObj" object. This shallow clone of "a" allows us to keep the original object unchanged while importing the properties into the new object.

Just like on line 6, we can now add new properties to our new object without fear of updating the original source object, "a".

*Just when you thought the humour couldn’t set a lower standard, the goat’s gone subterranean.

Joining two objects together in Google Apps Script

Probably the most useful part of using the spread operator is that we can join or concatenate two objects together.

In our next example, we will join objects "a" and "b" together to form one new object with our spread syntax. To do this we simply separate the two objects by a comma and use the spread syntax on both.

function wontYouJoinMe(){
  const a = {name:"Yagi", species:"goat"}
  const b = {hobby:"coding", friendly:true, action: ()=>{return 1 + 1}}

   const c = {...a,...b}; 
     console.log("a: ", a); //  a:  { name: 'Yagi', species: 'goat' }
     console.log("b: ", b); //  b:  { hobby: 'coding', friendly: true, action: [Function: action] }
     console.log("c: ", c);
      // c:  { 
      //  name: 'Yagi',
      //  species: 'goat',
      //  hobby: 'coding',
      //  friendly: true,
      //  action: [Function: action] }
}

function wontYouJoinMe(){

const a = {name:"Yagi", species:"goat"}

const b = {hobby:"coding", friendly:true, action: ()=>{return 1 + 1}}

const c = {...a,...b};

console.log("a: ", a); // a: { name: 'Yagi', species: 'goat' }

console.log("b: ", b); // b: { hobby: 'coding', friendly: true, action: [Function: action] }

console.log("c: ", c);

// c: {

// name: 'Yagi',

// species: 'goat',

// hobby: 'coding',

// friendly: true,

// action: [Function: action] }

}

As you can see, "c" has now combined "a" and "b" successfully while both "a" and "b" still maintain their individuality.

Adding an object to an existing object in Google Apps Script

One final thing you might find useful is to concatenate one or more objects to an existing object. We can do this with the Object. assign() method. This method takes a target object as its first parameter and then any number of objects as its subsequent parameters.

Adding one object to another.

Let’s say if I wanted to add all of "a"‘s properties to "b". It would look a little something like this:

function youWillBeAssimiated(){
  const a = {name:"Yagi", species:"goat"}
  const b = {hobby:"coding", friendly:true, action: ()=>{return 1 + 1}}
  

  Object.assign(b, a)
  b.name = 'collective'
  console.log("a: ", a); // a:  { name: 'Yagi', species: 'goat' }
  console.log("b: ", b);
  // b:  { 
  //   hobby: 'coding',
  //   friendly: true,
  //   action: [Function: action],
  //   name: 'Yagi',
  //   species: 'goat' }

}

function youWillBeAssimiated(){

const a = {name:"Yagi", species:"goat"}

const b = {hobby:"coding", friendly:true, action: ()=>{return 1 + 1}}

Object.assign(b, a)

b.name = 'collective'

console.log("a: ", a); // a: { name: 'Yagi', species: 'goat' }

console.log("b: ", b);

// b: {

// hobby: 'coding',

// friendly: true,

// action: [Function: action],

// name: 'Yagi',

// species: 'goat' }

}

As you can see, object "b" now has the properties of "a", but if we change the "name" property of "b" to something else, then "a" will not be affected.

Adding multiple objects to an existing object.

Likewise, we can add multiple objects to an existing object with the Object.assign() method. In the next example, we will make a copy of "a", "b" and "c" objects and join it to object "d".

function weAreTheBorg(){
  const a = {name:"All the single ladies", species:5618};
  const b = {hobby:"singing", friendly:true, action: ()=>{return 1 + 1}};
  const c = {leader:"Beyonce",title:"queen"};
  const d = {cube: 1184, compliment:3155};

  Object.assign(d, a, b, c)
  console.log("a: ", a); // a:  { name: 'Yagi', species: 'goat' }
  console.log("b: ", b); // b:  { hobby: 'singing', friendly: true, action: [Function: action] }
  console.log("c: ", c); // c:  { leader: 'Beyonce', title: 'queen' }
  console.log("d: ", d); 
  // d:  { 
  //   cube: 1184,
  //   compliment: 3155,
  //   name: 'All the single ladies',
  //   species: 5618,
  //   hobby: 'singing',
  //   friendly: true,
  //   action: [Function: action],
  //   leader: 'Beyonce',
  //   title: 'queen' }
};

function weAreTheBorg(){

const a = {name:"All the single ladies", species:5618};

const b = {hobby:"singing", friendly:true, action: ()=>{return 1 + 1}};

const c = {leader:"Beyonce",title:"queen"};

const d = {cube: 1184, compliment:3155};

Object.assign(d, a, b, c)

console.log("a: ", a); // a: { name: 'Yagi', species: 'goat' }

console.log("b: ", b); // b: { hobby: 'singing', friendly: true, action: [Function: action] }

console.log("c: ", c); // c: { leader: 'Beyonce', title: 'queen' }

console.log("d: ", d);

// d: {

// cube: 1184,

// compliment: 3155,

// name: 'All the single ladies',

// species: 5618,

// hobby: 'singing',

// friendly: true,

// action: [Function: action],

// leader: 'Beyonce',

// title: 'queen' }

};

As you have probably guessed by now, objects, "a","b" and "c" are not affected by the Object.assign() but now "d" has assimilated a copy of the properties of the other objects.

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.

The video.

https://youtu.be/MnuPwsMa8H8

Object assign doesn't work in Google Apps Script. Here's how to fix it. (https://youtu.be/MnuPwsMa8H8)

What’s going on?

Well, I found a relatively obscure reference to the reason why Object.create() performs how we expect it to in StackOverflow that was answered by a former legendary member of the Google Apps Script team, Corey Goldfeder.

Corey informs us that in Google Apps Script when objects are passed to Object.create() method, the properties of the original object are non-enumerable by default. This means that they cannot be explicitly iterated through with a for…in loop inside the create() method.

Corey goes on to say that Object.create() uses the Object.defineProperties() syntax which, unless the enumerable property is explicitly defined previously, will be defined as non-enumerable and not be added to the newly created object.

You could resolve or test this by explicitly defining the enumerability of each property in "a" as true which would then give you the result you were expecting.

function objectCreateExplicitEnumeration(){
  const a = {name:{value:"Yagi", enumerable:true}, species:{value:"goat", enumerable:true}}
  
  //This will work, but it is yucky.
  const newObj = Object.create({},a);
  newObj.foodAvailable = 100
  
  console.log("a: ", a);
  //a:  { name: { value: 'Yagi', enumerable: true },
  //      species: { value: 'goat', enumerable: true } }
  
  console.log("newObj: ", newObj);
  // newObj:  { name: 'Yagi', species: 'goat', foodAvailable: 100 }
}

function objectCreateExplicitEnumeration(){

const a = {name:{value:"Yagi", enumerable:true}, species:{value:"goat", enumerable:true}}

//This will work, but it is yucky.

const newObj = Object.create({},a);

newObj.foodAvailable = 100

console.log("a: ", a);

//a: { name: { value: 'Yagi', enumerable: true },

// species: { value: 'goat', enumerable: true } }

console.log("newObj: ", newObj);

// newObj: { name: 'Yagi', species: 'goat', foodAvailable: 100 }

}

Note the changes to the object 'a' and the parameters in the create method (Line 6).

That’s it for creating new objects based on existing objects in Google Apps Script. I would love to know how you used this code or if you have another approach. Feel free to share your ideas or questions in the comments below.

If you found the tutorial, useful why not shout me a cuppa.

~Yagi

How to programmatically schedule weekly time triggers between two dates in Google Apps Script

Carelessly left behind Google Apps Script time triggers can be greedy little blighters that can ultimately end in a whole lot of noggin scratching when your scheduled scripts decide not to run all of a sudden. Then there is a whole lot of house cleaning to remove all those time triggers you couldn’t be bothered setting a calendar reminder to remove them when you didn’t nee them any longer.

After all, there are some pretty tight quotas for the Consumer account (90 min) and if you are doing some heaving lifting in your Google Workspace account (6 hrs), then it will add up fast. Well… maybe I am just a glutton for triggers.

In this tutorial, we will cover how to schedule weekly time triggers between a period of dates in Google Apps Script programmatically using the Clock Trigger method of the ScriptApp class. The code basically sets all the triggers up on the desired range of dates and then removes all the triggers when the time expires.

Let’s get stuck into it!

The Code

Triggers.gs

This is the main code you will copy and paste into your own project. Read the Quick Use guide for what you will need to update for your own project.

/**
 * ##########################################################
 * Schedule a list of weekly time triggers with a start and
 * end date.
 * ##########################################################
 */

/**
 * An example run funtion that initializes the time triggers.
 * 
 * The format of the TIME_TRIGGER is essential. You can set
 * as many times setTimes as you want. 
 */
function runsies() {
  //List of time trigger data that we want to schedule.
  const TIME_TRIGGER = {
    // Set [year, month, day]
    startDate: [2021, 3, 8],// or "now" if it is immeidate.
    endDate: [2021, 3, 10],
    setTimes: [
      // [function, weekday, hour]
      // e.g. ["runFunciton", "Sunday", 18]
      ["refreshSheet", "Monday", 7],
      ["openAndSend", "Monday", 12],
      ["sendDeadlineReminder", "Wednesday", 7],
      ["removeEditorsFromSheet", "Wednesday", 15],
      //You can add more triggers.
    ]
  };

  timeTrigger.deploy(TIME_TRIGGER);
};


// ##########################################################

/**
 * Time trigger object. Containing the folloiwng functions: 
 * 
 *  - deploy : Runs the TIME_TRIGGER package.
 *  - setEndDateTrigger: Sets trigger for package expiry.
 *  - setTrigger : Creates weekly triggers from list.
 *  - remove : removes all triggers in project. 
 *  
 */
const timeTrigger = {

  /**
   * Initialises the trigger event
   * 
   * @param {Object} triggerData - a complext object containing star 
   * and end dates and times in the week to set.
   */
  deploy: (triggerData) => {
    timeTrigger.setEndDateTrigger(triggerData);

    //Check if start date is now
    if (triggerData.startDate.toLowerCase === "now") {
      timeTrigger.setTrigger(triggerData.setTimes)
      return;
    }

    triggerData.startDate[1] -= 1; // Minus 1 to get month index
    let startDate = new Date(...triggerData.startDate)

    let today = new Date();
    today.setHours(0, 0, 0, 0)
    
    //Check if date is today.
    if (startDate.getTime() == today.getTime()) {

      timeTrigger.setTrigger(triggerData.setTimes)
      return;

    }
    //If in the future will schedule a date to start the weekly triggers.
    else {

      ScriptApp.newTrigger("timeTrigger.setTrigger")
        .timeBased()
        .at(startDate)
        .create();
    };
  },

  /**
  * Sets a trigger to deletes the assigned weekly triggers.
  *
  * @param {Object} triggerData - a complext object containing star
  * and end dates and times in the week to set.
  */
  setEndDateTrigger: (triggerData) => {
    //Set last day to remove triggers
    triggerData.endDate[1] -= 1; // Minus 1 to get month index
    triggerData.endDate[2] += 1; // Move to following day
    let endDate = new Date(...triggerData.endDate);


    ScriptApp.newTrigger("timeTrigger.remove")
      .timeBased()
      .at(endDate)
      .create()
  },

  /**
  * Sets a weekly triggers for each setTimes item.
  *
  * @param {Array} times - 2d array each row containing trigger data
  * such as: [nextFunct, weekday, hour, minute]
  */
  setTrigger: (times) => {
    times.forEach((time) => {
      let [nextFunct, weekday, hour, minute] = time

      const wkDay = {
        Sunday: ScriptApp.WeekDay.SUNDAY,
        Monday: ScriptApp.WeekDay.MONDAY,
        Tuesday: ScriptApp.WeekDay.TUESDAY,
        Wednesday: ScriptApp.WeekDay.WEDNESDAY,
        Thursday: ScriptApp.WeekDay.THURSDAY,
        Friday: ScriptApp.WeekDay.FRIDAY,
        Saturday: ScriptApp.WeekDay.SATURDAY
      };

      ScriptApp.newTrigger(nextFunct)
        .timeBased()
        .onWeekDay(wkDay[weekday])
        .atHour(hour)
        .create()
    })
  },

  /**
  * Deletes all triggers in project. 
  *
  * Called when endDate trigger is activated. 
  */ 
  remove: () => {

    let triggers = ScriptApp.getProjectTriggers();
    triggers.forEach((trigger) => ScriptApp.deleteTrigger(trigger));
  }

};

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

/**

* ##########################################################

* Schedule a list of weekly time triggers with a start and

* end date.

* ##########################################################

/**

* An example run funtion that initializes the time triggers.

* The format of the TIME_TRIGGER is essential. You can set

* as many times setTimes as you want.

function runsies() {

//List of time trigger data that we want to schedule.

const TIME_TRIGGER = {

// Set [year, month, day]

startDate: [2021, 3, 8],// or "now" if it is immeidate.

endDate: [2021, 3, 10],

setTimes: [

// [function, weekday, hour]

// e.g. ["runFunciton", "Sunday", 18]

["refreshSheet", "Monday", 7],

["openAndSend", "Monday", 12],

["sendDeadlineReminder", "Wednesday", 7],

["removeEditorsFromSheet", "Wednesday", 15],

//You can add more triggers.

]

};

timeTrigger.deploy(TIME_TRIGGER);

};

// ##########################################################

/**

* Time trigger object. Containing the folloiwng functions:

* - deploy : Runs the TIME_TRIGGER package.

* - setEndDateTrigger: Sets trigger for package expiry.

* - setTrigger : Creates weekly triggers from list.

* - remove : removes all triggers in project.

const timeTrigger = {

/**

* Initialises the trigger event

* @param {Object} triggerData - a complext object containing star

* and end dates and times in the week to set.

deploy: (triggerData) => {

timeTrigger.setEndDateTrigger(triggerData);

//Check if start date is now

if (triggerData.startDate.toLowerCase === "now") {

timeTrigger.setTrigger(triggerData.setTimes)

return;

}

triggerData.startDate[1] -= 1; // Minus 1 to get month index

let startDate = new Date(...triggerData.startDate)

let today = new Date();

today.setHours(0, 0, 0, 0)

//Check if date is today.

if (startDate.getTime() == today.getTime()) {

timeTrigger.setTrigger(triggerData.setTimes)

return;

}

//If in the future will schedule a date to start the weekly triggers.

else {

ScriptApp.newTrigger("timeTrigger.setTrigger")

.timeBased()

.at(startDate)

.create();

};

/**

* Sets a trigger to deletes the assigned weekly triggers.

* @param {Object} triggerData - a complext object containing star

* and end dates and times in the week to set.

setEndDateTrigger: (triggerData) => {

//Set last day to remove triggers

triggerData.endDate[1] -= 1; // Minus 1 to get month index

triggerData.endDate[2] += 1; // Move to following day

let endDate = new Date(...triggerData.endDate);

ScriptApp.newTrigger("timeTrigger.remove")

.timeBased()

.at(endDate)

.create()

/**

* Sets a weekly triggers for each setTimes item.

* @param {Array} times - 2d array each row containing trigger data

* such as: [nextFunct, weekday, hour, minute]

setTrigger: (times) => {

times.forEach((time) => {

let [nextFunct, weekday, hour, minute] = time

const wkDay = {

Sunday: ScriptApp.WeekDay.SUNDAY,

Monday: ScriptApp.WeekDay.MONDAY,

Tuesday: ScriptApp.WeekDay.TUESDAY,

Wednesday: ScriptApp.WeekDay.WEDNESDAY,

Thursday: ScriptApp.WeekDay.THURSDAY,

Friday: ScriptApp.WeekDay.FRIDAY,

Saturday: ScriptApp.WeekDay.SATURDAY

};

ScriptApp.newTrigger(nextFunct)

.timeBased()

.onWeekDay(wkDay[weekday])

.atHour(hour)

.create()

})

/**

* Deletes all triggers in project.

* Called when endDate trigger is activated.

remove: () => {

let triggers = ScriptApp.getProjectTriggers();

triggers.forEach((trigger) => ScriptApp.deleteTrigger(trigger));

}

};

ExampleFuncitons.gs

This file is just an example of the functions I have assigned for my project in my runsies() function.

/**
 * ##########################################################
 * Example list of functions that will run on weekly triggers
 * ##########################################################
 */



function refreshSheet() {
  // Copy and paste data to repository file.

  console.log("Copy and paste data to repository file.");
};

function openAndSend() {
  // Share edit permission and send a group email to team to edit the sheet. 

  console.log("Share edit permission and send a group email to team to edit the sheet.");
};

function sendDeadlineReminder() {
  // Send a deadline reminder to users who have note completed the task 1 day before deadline.

  console.log("Send a deadline reminder to users who have note completed the task 1 day before deadline.");
};

function removeEditorsFromSheet() {
  //remove editors from sheet on deadline. 

  console.log("remove editors from sheet on deadline.")
};

/**

* ##########################################################

* Example list of functions that will run on weekly triggers

* ##########################################################

function refreshSheet() {

// Copy and paste data to repository file.

console.log("Copy and paste data to repository file.");

};

function openAndSend() {

// Share edit permission and send a group email to team to edit the sheet.

console.log("Share edit permission and send a group email to team to edit the sheet.");

};

function sendDeadlineReminder() {

// Send a deadline reminder to users who have note completed the task 1 day before deadline.

console.log("Send a deadline reminder to users who have note completed the task 1 day before deadline.");

};

function removeEditorsFromSheet() {

//remove editors from sheet on deadline.

console.log("remove editors from sheet on deadline.")

};

Quick use guide

Here, we will quickly go through using the script to get you up and running.

The Example

In the example, we have an imaginary document that needs to be edited by our team. If you look at the ExampleFuncitons.gs file you can see our list of time trigger task for our Google Apps Script project as follows:

Refresh the Google Sheet: We possibly need to send a report and clear it out at a certain time to set it up for the next week.
Open and send an access email: We will send off an email to our team to let them know that they now have edit access for the sheet and complete their weekly task.
Send a deadline reminder: Just before the deadline, we send out a reminder to our stragglers that the deadline is due so that they can get unnecessarily offended. 🙄🐐
Remove the editors from the sheet: Once the deadline hits, we revoke edit access for our team from the sheet to maintain the integrity of the sheet before we send off our report.

Before you add your trigger times

A note on time zones

Just hold up one second. Before you add your trigger times, I recommend that you double-check the time zone assigned to your project.

Didn’t know that was a thing? No worries. Check out this short video on how to get that done fast:

https://youtu.be/nKIJchyAOus

Change the time zone of your Google Apps Script Project: Why your triggers aren't running on time (https://youtu.be/nKIJchyAOus)

And you can dive a little deeper here:

Help! My time triggers are not in sync! : How to update your Google Apps Script project time zone.

Adding the script into your project

I recommend creating a separate *.gs file to add in the timeTrigger object from lines 35-145. It just gets it out of the way of your awesome project codes.

The runsies() function is just an example function. If all you want to do is add your triggers days and dates in and hit run, then it is fine. But if you want to programmatically draw your start and end trigger dates and weekly trigger times from somewhere else then all you need to add into your own function is the TIME_TRIGGER objects and then run:

timeTrigger.deploy(TIME_TRIGGER);

Adding your times and date window

  //List of time trigger data that we want to schedule.
  const TIME_TRIGGER = {
    // Set [year, month, day]
    startDate: [2021, 3, 8],// or "now" if it is immeidate.
    endDate: [2021, 3, 10],
    setTimes: [
      // [function, weekday, hour]
      // e.g. ["runFunciton", "Sunday",20]
      ["refreshSheet", "Monday", 7],
      ["openAndSend", "Monday", 12],
      ["sendDeadlineReminder", "Wednesday", 7],
      ["removeEditorsFromSheet", "Wednesday", 15],
      //You can add more triggers.
    ]
  };

//List of time trigger data that we want to schedule.

const TIME_TRIGGER = {

// Set [year, month, day]

startDate: [2021, 3, 8],// or "now" if it is immeidate.

endDate: [2021, 3, 10],

setTimes: [

// [function, weekday, hour]

// e.g. ["runFunciton", "Sunday",20]

["refreshSheet", "Monday", 7],

["openAndSend", "Monday", 12],

["sendDeadlineReminder", "Wednesday", 7],

["removeEditorsFromSheet", "Wednesday", 15],

//You can add more triggers.

]

};

There are two parts to this object. First, set the start and end date that you want to run your weekly triggers. Lines 4 & 5

If you want to start your triggers straight away, then you can enter “now”, otherwise enter in a date. You will get an error message if your date is before the current date.

The end date removes all the time triggers in your project. So if you have other triggers, you will need to make some changes to the timeTrigger object (Maybe register each trigger id in PropertiesSerivce).

To add your dates, start with the year, month and date. Note that unlike the weirdness of the Javascript Date() constructor, I have made the month the common number. So a 3 will be March (not April in the Date() constructor).

Next, add the weekly triggers that you want to run each week for this project. Lines 21-28

You can add in as many as you want here. In the example, I have added four. Each piece of weekly trigger data is contained in an array:

[function, weekday, hour]

function: The function that you want to be triggered.
weekday: Full word days of the week from Monday to Sunday.
hour: The hour of the day that you want to run the trigger.

Deploying and scheduling the time trigger

To schedule, your triggers run the timeTrigger.deploy(TIME_TRIGGER); function.

If you want to test things before your first deploy, you can check the triggers are all set up you can check the triggers in your Apps Script menu (1).

List of triggers in Google Apps Script Editor IDE — Click to Expand!

To check the times of your trigger, you can click on the vertical ellipses beside each trigger (2).

You can delete all the triggers in your project and start again with:

timeTrigger.remove();

That’s pretty much all you need to know to get this script up and running in your project.

If you want to learn more about how the timeTrigger object was written and how to code Clock Triggers, jump down into the next header.

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

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

Code breakdown for timeTrigger

timeTrigger.deploy()

...

  /**
   * Initialises the trigger event
   * 
   * @param {Object} triggerData - a complext object containing star 
   * and end dates and times in the week to set.
   */
  deploy: (triggerData) => {
    timeTrigger.setEndDateTrigger(triggerData);

    //Check if start date is now
    if (triggerData.startDate.toLowerCase === "now") {
      timeTrigger.setTrigger(triggerData.setTimes)
      return;
    }

    triggerData.startDate[1] -= 1; // Minus 1 to get month index
    let startDate = new Date(...triggerData.startDate)

    let today = new Date();
    today.setHours(0, 0, 0, 0)
    
    //Check if date is today.
    if (startDate.getTime() == today.getTime()) {

      timeTrigger.setTrigger(triggerData.setTimes)
      return;

    }
    //If in the future will schedule a date to start the weekly triggers.
    else {

      ScriptApp.newTrigger("timeTrigger.setTrigger")
        .timeBased()
        .at(startDate)
        .create();
    };
  },

...

...

/**

* Initialises the trigger event

* @param {Object} triggerData - a complext object containing star

* and end dates and times in the week to set.

deploy: (triggerData) => {

timeTrigger.setEndDateTrigger(triggerData);

//Check if start date is now

if (triggerData.startDate.toLowerCase === "now") {

timeTrigger.setTrigger(triggerData.setTimes)

return;

}

triggerData.startDate[1] -= 1; // Minus 1 to get month index

let startDate = new Date(...triggerData.startDate)

let today = new Date();

today.setHours(0, 0, 0, 0)

//Check if date is today.

if (startDate.getTime() == today.getTime()) {

timeTrigger.setTrigger(triggerData.setTimes)

return;

}

//If in the future will schedule a date to start the weekly triggers.

else {

ScriptApp.newTrigger("timeTrigger.setTrigger")

.timeBased()

.at(startDate)

.create();

};

...

The timeTrigger.deploy() function is the main run function and takes the object of start and end dates and weekly triggers that we assigned in the TIME_TRIGGER object in the runsies() function.

Its first task is to set the date the weekly triggers need to be removed. This is done with the setEndDateTrigger(triggerData) function that we will discuss in a minute.

Check if start dat is now

Next, we need to check the input for the triggerData.startDate. If the user selected “Now”, then we immediately run timeTrigger.setTrigger() and complete the script. Lines 12-16

Check if start date is today

Alternatively, if the user puts in the current day’s date then we need to run the timeTrigger.setTrigger() straight away too. First, we need to transform the users triggerData.StartDate data into a readable date. We do this using the Javascript new Date() constructor that has the option to take the format; year, month, date:

new Date(year, month index, day of month)

Because the month index for Date() starts at 0 for January and ends at 11 for December, so we need to subtract 1 from the users month input before creating the date. Line 18

I used a Javascript spread operator (…array) to add in all the values of triggerData.StartDate which in our example are:

[2021, 3, 8]

So:

new Date(...triggerData.startDate)

Is actually, this:

new Date(2021, 3, 8)

Which is much tidier than:

new date(triggerData.startDate[0], triggerData.startDate[1], triggerData.startDate[2])

I’m really digging the spread operator lately.

Next, we need to compare the current date with the triggerData.startDate. We can do this by converting the two dates to times using the getTime() method. This transforms the dates into long number values that can be compared. Line 25

Before we do this though we kinda need to clear the current time out of the current data otherwise we won’t be able to compare things properly (line 26). When we run new Date() to create the today variable it will give us the date and the current time all the way down to milliseconds. However when we created the date for our scheduled date we only added the year, month and day so the time will be set to midnight.

Let’s update today date by changing the time of the day to midnight with:

today.setHours(0, 0, 0, 0);

If start date is in the future, schedule it!

Our final condition is if the user has scheduled a date in the future. We don’t want to clutter up their trigger quota unnecessarily so we need to postpone our triggers. To do this we ironically, need to create a clock trigger that runs only once on the date we want our weekly triggers to start.

To programmatically set a time trigger in Google Apps Script we call the newTrigger method in the ScriptApp class. Line 34

The newTrigger method takes our assigned function as an argument. This is the function we want to run when our trigger goes off. For us, this is the timeTrigger.setTrigger() function that will build the weekly triggers.

Running setTrigger() also starts the process of building the trigger.

Your next step is to decide what type of trigger you want. In our project, we want a timeBased() trigger. Line 35

Selecting timeBased() takes us to the Clock Trigger builder, where we can choose from a bunch of settings. For us, all we want to set is a start date so we chose the at(date) method which takes a constructed Javascript date. Here we throw in the startDate variable we built on line 19.

Once we have all our trigger data inputted, we need to create() our trigger.

timeTrigger.setEndDateTrigger()

...
 
 /**
  * Sets a trigger to deletes the assigned weekly triggers.
  *
  * @param {Object} triggerData - a complext object containing star
  * and end dates and times in the week to set.
  */
  setEndDateTrigger: (triggerData) => {
    //Set last day to remove triggers
    triggerData.endDate[1] -= 1; // Minus 1 to get month index
    triggerData.endDate[2] += 1; // Move to following day
    let endDate = new Date(...triggerData.endDate);


    ScriptApp.newTrigger("timeTrigger.remove")
      .timeBased()
      .at(endDate)
      .create()
  },

...

...

/**

* Sets a trigger to deletes the assigned weekly triggers.

* @param {Object} triggerData - a complext object containing star

* and end dates and times in the week to set.

setEndDateTrigger: (triggerData) => {

//Set last day to remove triggers

triggerData.endDate[1] -= 1; // Minus 1 to get month index

triggerData.endDate[2] += 1; // Move to following day

let endDate = new Date(...triggerData.endDate);

ScriptApp.newTrigger("timeTrigger.remove")

.timeBased()

.at(endDate)

.create()

...

The timeTrigger.setEndDateTrigger() function takes the triggerData as an argument. From that, it extracts the endDate array of year month and day.

Just like in timeTrigger.deploy(), we need to take one away from the month to get the proper value for Javascript. Then we want to remove our weekly triggers basically on the midnight of the following day after the user’s recorded end date. Lines 11-12

Why? Well, the end date usually means that it is a date the final trigger will occur on. We don’t want to remove our trigger before that date though or we will mess up our user’s process.

Once we have created our date we go through the same process as scheduling a start date in the future like we did in timeTrigger.deploy().

timeTrigger.setTrigger()

...

  /**
  * Sets a weekly triggers for each setTimes item.
  *
  * @param {Array} times - 2d array each row containing trigger data
  * such as: [nextFunct, weekday, hour, minute]
  */
  setTrigger: (times) => {
    times.forEach((time) => {
      let [nextFunct, weekday, hour] = time

      const wkDay = {
        Sunday: ScriptApp.WeekDay.SUNDAY,
        Monday: ScriptApp.WeekDay.MONDAY,
        Tuesday: ScriptApp.WeekDay.TUESDAY,
        Wednesday: ScriptApp.WeekDay.WEDNESDAY,
        Thursday: ScriptApp.WeekDay.THURSDAY,
        Friday: ScriptApp.WeekDay.FRIDAY,
        Saturday: ScriptApp.WeekDay.SATURDAY
      };

      ScriptApp.newTrigger(nextFunct)
        .timeBased()
        .onWeekDay(wkDay[weekday])
        .atHour(hour)
        .create()
    })
  },

...

...

/**

* Sets a weekly triggers for each setTimes item.

* @param {Array} times - 2d array each row containing trigger data

* such as: [nextFunct, weekday, hour, minute]

setTrigger: (times) => {

times.forEach((time) => {

let [nextFunct, weekday, hour] = time

const wkDay = {

Sunday: ScriptApp.WeekDay.SUNDAY,

Monday: ScriptApp.WeekDay.MONDAY,

Tuesday: ScriptApp.WeekDay.TUESDAY,

Wednesday: ScriptApp.WeekDay.WEDNESDAY,

Thursday: ScriptApp.WeekDay.THURSDAY,

Friday: ScriptApp.WeekDay.FRIDAY,

Saturday: ScriptApp.WeekDay.SATURDAY

};

ScriptApp.newTrigger(nextFunct)

.timeBased()

.onWeekDay(wkDay[weekday])

.atHour(hour)

.create()

})

...

This trigger is initialised from either the timeTrigger.deploy() straight away if the start date is the current date or is scheduled for deployment at a later date.

The timeTrigger.setTrigger() takes the 2d array of all the weekly triggers assigned by the user. In our example, that was this:

...
    setTimes: [
      // [function, weekday, hour]
      // e.g. ["runFunciton", "Sunday", 18]
      ["refreshSheet", "Monday", 7],
      ["openAndSend", "Monday", 12],
      ["sendDeadlineReminder", "Wednesday", 7],
      ["removeEditorsFromSheet", "Wednesday", 15],
      //You can add more triggers.
    ]
...

...

setTimes: [

// [function, weekday, hour]

// e.g. ["runFunciton", "Sunday", 18]

["refreshSheet", "Monday", 7],

["openAndSend", "Monday", 12],

["sendDeadlineReminder", "Wednesday", 7],

["removeEditorsFromSheet", "Wednesday", 15],

//You can add more triggers.

]

...

First, it loops through each set of trigger input data with a forEach loop.

Assigning variables using destructuring

I want to assign a variable for each item in the currently iterated array so that I can work with it in building the trigger. Here I used a destructuring assignment now available in Google Apps Script V8 runtime. Line 11

If you are unfamiliar with the destructuring assignment, you can basically set an array of variables – in our case – on the left and assign (=) them to a corresponding array of data on the right. So:

let [nextFunct, weekday, hour] = time

Would assign these values to the varialbes on the first iteration:

let [nextFunct, weekday, hour] = ["refreshSheet", "Monday", 7]

Without destructuring this variable assignment might look like this:

let nextFunct = time[0];
let weekday = time[1];
let hour = time[2];

let nextFunct = time[0];

let weekday = time[1];

let hour = time[2];

Pretty cool, I reckon.

PReparing the days of the week

Next, we need to assign a day of the week to our clock trigger. These assignments require an enumerator which is basically:

ScriptApp.WeekDay.A_DAY_OF_THE_WEEK _IN_ALL_CAPS

Fortunately for us, we assigned our variable weekday to time[1] which is the day of the week for this iteration.

Lines 13-21

Creating the weekly clock triggers

Finally, we create our first clock trigger. Just like our date triggers we first call ScriptApp.newTrigger(nextFunct) where nextFunct is the time[0] value the user assigned as their function that they want to run on their trigger.

Again, we set the trigger to timeBased() but this time around we use onWeekDay() method. This method takes one of those weekday enumerators we store in our wkDay variable. We will select the correct one by adding weekday variable inside weekday:

wkDay[weeday] Line 25

For weekday triggers we can also set the hour of the day. This will deliver the trigger close to the hour selected. Line 26

timeTrigger.remove()

...

  /**
  * Deletes all triggers in project. 
  *
  * Called when endDate trigger is activated. 
  */ 
  remove: () => {

    let triggers = ScriptApp.getProjectTriggers();
    triggers.forEach((trigger) => ScriptApp.deleteTrigger(trigger));
  }

...

...

/**

* Deletes all triggers in project.

* Called when endDate trigger is activated.

remove: () => {

let triggers = ScriptApp.getProjectTriggers();

triggers.forEach((trigger) => ScriptApp.deleteTrigger(trigger));

}

...

The timeTrigger.remove() function runs on the end date. Alternatively, you can run this to remove all of your triggers if you are testing on making a mistake.

To remove your triggers, you will first need to get all of your projects triggers. We do this with ScriptApp.getProjectTriggers();Then, you will need to loop through each trigger and delete the trigger using the deleteTrigger() method that takes the trigger object.

Conclusion

I have been deploying triggers like this for a while now after and…erhm…unfortunate incident with meeting my quota and being lazy and leaving old triggers active. This now saves me a lot of future grief.

One thing you might have to adjust for a bigger project is the timeTrigger.Remove() method. If you have other triggers running in the same project, you probably don’t want to delete them. You could use the properties service to store your trigger ids and then check them against the list of all triggers to determine if they need to be deleted.

Another thing worth considering is that you might want to schedule multiple dates to run your triggers. You would need to loop through each start and end date here and create a trigger for each one.

Anyway, if you found this useful please give it a like in the comments below and if you have a plan to deploy it in your own project or a version of it I would love to hear about it in the comments.

Happy coding!

~Yagi

Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)

Google Apps Script, Google Sheets, Javascript

Have you ever wanted to have a Google Sheet available for only those users who need to edit and then once they are done, take their edit permissions away to maintain the integrity of the sheet?

I know that I have come across these conditions a number of times in the past. Maybe you have a task list that you want to send your team each time a task is allocated to them. Once they let you know that they are done, by say, entering a set of values or checking a task complete box on the row they need to work on, you want to be able to remove their edit permissions from your sheet.

In this post, we have created a Google Apps Script that will add and remove editors to a Google Sheet based on the spreadsheet’s data. More specifically, this script will:

Grab the users name and email in each row along with whether or not they have complete the task or if the Google Sheet has been shared and sent to the user.
Share the assigned user to the Google Sheet.
Send an email to the user. A separate Google Sheet tab is added to the sheet so an administrator can add their custom email message.
Automatically check a reference column of checkboxes indicating that the assigned user has been shared as an editor on the Google Sheet and an email has been sent to them.
Once the task has been complete the user check the “Edit Complete” checkbox in their assigned row.
Either automatically each day or when the Google Sheets administrator clicks the button, each user who has completed all assigned tasks is removed from having edit permissions to the Google Sheet.

The best way to probably understand this script is through an example…

Continue reading “Google Apps Script: How to Add and Remove Editors to a Google Sheet based on Sheet Data (Updated Feb 2022)”

Share this:

Like this:

Share this:

Like this:

Some Solutions

Spread Syntax to the rescue

If you create a new object and add a property to it

Joining two objects together in Google Apps Script

Adding an object to an existing object in Google Apps Script

Adding one object to another.

Adding multiple objects to an existing object.

The video.

What’s going on?

Share this:

Like this:

The Code

Triggers.gs

ExampleFuncitons.gs

Quick use guide

The Example

Before you add your trigger times

A note on time zones

Adding the script into your project

Adding your times and date window

Deploying and scheduling the time trigger

Code breakdown for timeTrigger

timeTrigger.deploy()

Check if start dat is now

Check if start date is today

If start date is in the future, schedule it!

timeTrigger.setEndDateTrigger()

timeTrigger.setTrigger()

Assigning variables using destructuring

PReparing the days of the week

Creating the weekly clock triggers

timeTrigger.remove()

Conclusion

Share this:

Like this:

Share this:

Like this: