If you’ve been using Apps Script for a while, since back when it was running on the Rhino JavaScript emulator; see (What JavaScript engine is Apps Script running on?), you’ll remember the Execution Transcript. This was a really useful log of every call made to built-in Apps Script services, along with the arguments passed and the result. It was a great tool for debugging.

With V8, that disappeared, so I wondered if there might be a way to intercept calls to Apps Script services to bring back what we lost with the disappearance of the Execution Transcript. And, of course we can! With a minimum of coding, we can fiddle around with any Gas Service call. This article will show you how.

Page Content hide

2 What is Proxy

3 Setting up a proxy

4 Changing get behavior with a proxy

5 Traps

5.1 Apply trap

6 Reflect

7 Recreating the Execution transcript

8 Spreadsheet examples

9 Setting up Spreadsheet proxies

10 Example transcripts

11 Limitations

12 Opportunities

13 Summary

Approach

There were 2 approaches I considered

A wrapper around every function that did some logging around every function call. I dismissed this because it would be a mammoth task, and would involve some change of structure to use.
Use Es6 Proxy to trap calls to Apps Script Services and automatically add logging every time a method was invoked. This involves minimal coding and change of structure.

Obviously I picked option 2, and that’s what I’ll cover here.

What is Proxy

It’s a new Global Object that appeared in ES6, and it allows you to intercept the fundamental workings of a JavaScript object to change its behavior. These intercepts are called traps.

For example, accessing a property like this from an object fires off an internal get of the ‘name’ property in the target object

  const person = {
    name: "Bond",
    firstName: "James",
    id: '007'
  }
  
  // Bond
  console.log(person.name)

object get

Setting up a proxy

We can use a proxy for our person object, which will allow us to ‘trap’ a get request, and then fiddle with it. This example does nothing other than return person.name, just as the original object would do

const proxy = new Proxy (person, {
	get ( target , property, receiver) {
		return target[property]
	}
})

// this 
const x = proxy.name

// is exactly the same result as
const x = person.name

get trap with proxy

Changing get behavior with a proxy

However we could fiddle with the get and return something different


  const proxy = new Proxy(person, {
    get(target, property, receiver) {
      if (property === 'name') {
        return `the name is ${target[property]}, ${target.firstName} ${target[property]}`
      }
      else {
        return target[property]
      }
    }
  })

  // the name is Bond, James Bond
  console.log(proxy.name)
  // 007
  console.log(proxy.id)
  // James
  console.log(proxy.firstName)

fiddle with the get result

Traps

In this article, the scope is about changing the behavior of Apps Script methods – so I’m only going to cover 2 traps

get – when a property is accessed – already referenced in the examples above
apply – when a function is run

However there are a whole bunch of other traps available – for a full list see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Proxy

I’ll no doubt return to some of these in future articles

Apply trap

The get trap applies to objects, but we can also change the behavior of functions using the apply trap. Consider this function

const adder = (a,b) => a b

// 50
const x = adder (20,30)

function example

For some reason or another, let’s say we want to create a transcript logging every time it’s called and what the result was without changing the actual function. That’s exactly what the apply trap of a Proxy is for.

  const adderProxy = new Proxy( adder , {
    apply (targetFunction, thisArg, args) {
      const result = targetFunction.apply(thisArg,args)
      console.log('adding',args,'result',result)
      return result
    }
  })
  // 2:22:48 PM	Info	adding [ 20, 30 ] result 50
  const y = adderProxy (20,30)

function apply trap

Reflect

Most of the time, this example will return the correct result

const proxy = new Proxy (person, {
	get ( target , property, receiver) {
		return target[property]
	}
})

// this 
const x = proxy.name

// is exactly the same result as
const x = person.name

get trap with proxy

However if the original object contains a get() property, under certain circumstances (which is outside the scope of this article) confusion about ‘this’ will produce the wrong result. You’ll notice that there’s a ‘receiver’ argument to the get trap – it is required to maintain the correct context for ‘this’ in complex objects.

Luckily the Es6 Reflect object can take care of all of that and it takes the same arguments as those received by the get trap. So I recommend always using Reflect to return the contents of a property in a get trap, rather than accessing the property directly – like this

const proxy = new Proxy (person, {
	get ( target , property, receiver) {
		// dont do this
		// return target[property]
		// do this instead
		return Reflect.get( target, property, receiver)
	}
})

// this 
const x = proxy.name

// is exactly the same result as
const x = person.name

get trap with proxy and use Reflect

Recreating the Execution transcript

The above gives us all the tools we need to intercept all Apps Script service calls and to make a transcsript of each time they are called.

Here’s an example of the kind of log you’ll be able to make following this tutorial

3:03:16 PM	Info	1627653796109:SpreadsheetApp.openById [ '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ' ] {}  (728ms)
3:03:16 PM	Info	1627653796844:Spreadsheet.getSheetByName [ 'airport list' ] {}  (8ms)
3:03:17 PM	Info	1627653796855:Sheet.getDataRange [] {}  (215ms)
3:03:17 PM	Info	1627653797072:Range.getA1Notation [] A1:I557  (1ms)
3:03:17 PM	Info	1627653797075:Range.getValues [] [ [ 'name',
    'latitude_deg',
    'longitude_deg',
    'elevation_ft',
    'iso_country',
    'iso_region',
    'municipality',
    'iata_code',
    'timestamp' ],
  [ 'Port Moresby Jacksons International Airport',
    -9.44338035583496,
    147.220001220703,
    146,
    'PG',
    'PG-NCD',
    'Port Moresby',
    'POM',....

example execution transcript

Spreadsheet examples

You can apply these techniques to any Apps Script objects, but for this example I’m going to set up proxies for

SpreadsheetApp
Spreadsheet
Sheet
Range

These 4 proxies will all us to log access by all methods for the main spreadsheet objects. If you’ve read other of my tutorials, you’ll be familiar with the contents of the sheet I’ll be using for this demo

const tester = {
  id: '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ',
  sheetName: 'airport list'
}

tester sheet

Setting up Spreadsheet proxies

So instead of using the native objects (for example SpreadsheetApp), we’ll reference everything through the proxies for those objects

  // add a proxy to the SpreadsheetApp
  const proxySpreadSheetApp = new Proxy(SpreadsheetApp, gasServiceProxyHandler)
  const spreadSheet = proxySpreadSheetApp.openById(tester.id)

  // add a proxy to the spreadsheet
  const proxySpreadsheet = new Proxy(spreadSheet, gasServiceProxyHandler)
  const sheet = proxySpreadsheet.getSheetByName(tester.sheetName)

  // add a proxy to the sheet
  const proxySheet = new Proxy(sheet, gasServiceProxyHandler)
  const range = proxySheet.getDataRange()

  // add a proxy to the range
  const proxyRange = new Proxy(range, gasServiceProxyHandler)

setting up sheet proxies

GasProxyHandler

You’ll notice that each proxy uses exactly the same handler, since we’re doing the same thing for each – namely adding some generalized logging any time any of their methods are called

/**
 * a handler to trap calls to service functions
 * should be used like this
 * const proxy = new Proxy (GasServiceObject , gasServiceProxyHandler)
 * @returns {void}
 */
const gasServiceProxyHandler = {
  // this traps the get
  // if it's a function, then we need to generate an apply trap
  // and return a proxy to that
  get(target, property, receiver) {
    const value = Reflect.get(target, property, receiver)
    if (typeof value !== 'function') {
      return value
    }
    // so now we need to fiddle with the execution of the function
    // to do this rather than it's normal behavior
    return new Proxy(value, {
      apply(targetFunction, thisArg, args) {
        const pack = timeIt(() => targetFunction.apply(thisArg, args))
        logIt({ target, property, args, pack })
        if (pack.error) throw new Error(pack.error)
        return pack.result
      }
    })
  }
}

proxy handler

Explanation

Imagine that you called sheet.getName(). 2 traps would be fired

the get trap for the property – getName – which happens to be a method and would return a function
the apply trap for the method getName

The trick here is to intercept the get trap and if the value of the property requested is not a function, then simply Reflect back the value of that object property. However, if the value of the property requested is a function, we’ll return a proxy to that function, which has an apply trap to not only run the the originally requested function but also to log details about what happened, and information about how it was called – in other words, the execution transcript.

You’ll notice that I call a couple of other functions here – which are just to decorate the transcript – so you can prettify them as you want. The key thing is to apply the underlying function and the return the result – everything else is just logging decoration

return targetFunction.apply(thisArg, args)

applying the function

Decoration

timeIt

wraps the execution of a function calculates the elapsed time

/**
 * This is the result of a timeIt
 * @typedef {Object} TimeItResponse
 * @property {number} startedAt - the start time
 * @property {number} finishedAt - the finish time
 * @property {*} result - of calling the function
 * @property {Error|null} error -  the error
 * @property {number} elaped - the runtime of the function
 */

/**
 * execute a function and time how long it takes
 * @param {function} func the thing to execute
 * @returns {TimeItResponse} the result and stats
 */
const timeIt = (func) => {
  const startedAt = new Date().getTime()
  let result = null
  let error = null
  try {
    result = func()
  }
  catch (err) {
    error = err
  }
  const finishedAt = new Date().getTime()
  const elapsed = finishedAt - startedAt
  return {
    startedAt,
    finishedAt,
    result,
    error,
    elapsed
  }
}

timeIt

logIt

logs the transscript in whatever format suits – this is just a very basic rendering

/**
 * logs a call to a gas service
 * @param {object} item the stuff to log
 * @param {number} [maxRows = 4] if the result is too long we can constrain it (falsey=don't constrain)
 * @param {*} args the args to the function to log
 * @param {string} property the property name
 * @param {function} target the service function
 * @param {TimeIt} pack the result and stats
 */
const logIt = (item, maxRows = 4) => {
  const { args, property, target, pack } = item
  const { startedAt, elapsed, result, error } = pack
  const p = error || result
  const overflow = maxRows && Array.isArray(p) && p.length > maxRows
  const logResult = overflow ? p.slice(0, maxRows) : p
  console.log(`${startedAt}:${target.toString && target.toString()}.${property}`, args, logResult, overflow ? '...' : '', `(${elapsed}ms)`)
  return item
}

logIt

Example transcripts

Here’s a few examples of the kind of transcripts generated

//1627659097103:SpreadsheetApp.openById [ '1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ' ] {}  (3ms)  
  proxySpreadsheetApp.openById(tester.id)
//1627659097111:Spreadsheet.getSheetByName [ 'airport list' ] {}  (2ms)  
  proxySpreadsheet.getSheetByName(tester.sheetName)
//1627659097116:Sheet.getName [] airport list  (0ms)
  proxySheet.getName()
//1627659097119:Sheet.getDataRange [] {}  (176ms)
  proxySheet.getDataRange()
// 1627659097297:Sheet.getRange [ 1, 2, 3, 4 ] {}  (3ms)
  proxySheet.getRange(1,2,3,4)
// 1627659097302:Range.getA1Notation [] A1:I557  (1ms)
  proxyRange.getA1Notation()
// 1627659097305:Range.getValues [] [ [ 'name', 'latitude_deg','longitude_deg','elevation_ft','iso_country','iso_region',... etc (194ms)
  proxyRange.getValues()
//1627659097510:Range.offset [ 1, 2, 3, 4 ] {}  (4ms)
  proxyRange.offset(1,2,3,4)

example transcripts

Limitations

Chaining is not going to log every part of the chain – for example the chained getA1Notation() part is not logged. This is because the returned range from .offset is a plain Range ie. not one that has been proxied.


// 1627659097510:Range.offset [ 1, 2, 3, 4 ] {}  (4ms)
  proxyRange.offset(1,2,3,4).getA1Notation()

chaining

So if you wanted both parts of the chain to be logged you need to proxy the result of the offset too

//1627659097518:Range.offset [ 1, 2, 3, 4 ] {}  (2ms)
//1627659097522:Range.getA1Notation [] C2:F4  (1ms) 
new Proxy(proxyRange.offset(1,2,3,4), gasServiceProxyHandler).getA1Notation()

chaining transcript

Opportunities

If you tweaked the logging to collect stats by function, you’d have an excellent basis for profiling and visualizing your script to look for functions and code that could be optimized, but that’s for another post.

Summary

If you want something to produce a transcript – proxy it and use the proxy.

And that’s it – the execution transcript resurrected with very little coding

Desktop Liberation

The definitive resource for Google Apps Script and Microsoft Office automation

Resuscitating the Apps Script execution transcript – JavaScript Proxy and Reflect to the rescue

Approach

What is Proxy

Setting up a proxy

Changing get behavior with a proxy

Traps

Apply trap

Reflect

Recreating the Execution transcript

Spreadsheet examples

Setting up Spreadsheet proxies

GasProxyHandler

Explanation

Decoration

timeIt

logIt

Example transcripts

Limitations

Opportunities

Summary