Multiple Script Files: The problem

Apps Script V8 doesn’t have a module loader system. If you’re used to developing in NodeJs, you’ll also be familiar with importing and exporting to include required modules in your project. With Apps Script, you have no control over the order in which multiple script files are executed. In Legacy Apps Script, there seemed to be some kind of workaround going on so that global statements were executed in a sensible order (I don’t know the details), but in V8 this is not the case.

Global space

I’ve always advocated against defining anything in the Global space, and certainly nothing executable, and this is even more important with V8 where you can make no assumptions about the order. For this reason, I’ve always recommended that you use namespacing Improved namespace pattern for Apps Script both to avoid naming collisions but also to ensure correct execution order. This (longish) article will be a Sheets project to demonstrate how you can reliably use multiple scripts in V8.

Structure

This small project will include

  • class to abstract Sheets
  • class to objectify values in sheets
  • Singletons for managing access to various APIs
  • Singleton to manage app settings
  • App function to control the execution

In other words – this

I’ll be using a sheet which has some airport data, like this

We’ll fill in the details later, but here are the things the App will do. This will be the only executable function in the entire project. Everything else will be inside namespaces.

const App = () => {
  // open the airports sheet

  // convert sheet to object

  // find all the rows with airports at altitude > 3000 metres
  
  // get a handle to another sheet (and create it if it doesnt exist)

  // replace current values (if any) with amended data from the other sheet
  // write out new values with metres and only airports > 2000 metres
  
  // enrich data with sunrise/sunset info from an api
 
  // and write it back
 
}

 

Settings

Like all namespaces, Settings is declared as an IEF (immediately executing function), and will contain all the parameter information required to run the App.

const Settings = (()=>{
  return {
    apis: {
      sunset: {
        getUrl: function (lat, lon) {
          return 'https://api.sunrise-sunset.org/json?lat=99&lng=99'
            .replace(/lat=\d+/,`lat=${lat}`).replace(/lng=\d+/,`lng=${lon}`)
        }
      },
      weather: {
        getUrl: function (lat, lon) {
          return 'https://fcc-weather-api.glitch.me/api/current?lat=99&lon=99'
            .replace(/lat=\d+/,`lat=${lat}`).replace(/lon=\d+/,`lon=${lon}`)
        }
      }
    },
    sheets: {
      airports: {
        sheetId: '15MDlPLVH4IhnY2KJBWYGANoyyoUFaxeWVDOe-pupKxs',
        sheetName: 'large airports'
      },
      metres: {
        sheetId: '15MDlPLVH4IhnY2KJBWYGANoyyoUFaxeWVDOe-pupKxs',
        sheetName: 'high airports with metres',
        create: true
      }
    }
  }
})()

Note the syntax of an IEF – this imitates the behavior of  module and export as you’d find in NodeJs

const namespaceName = (() => {
   // .. any code
   return {
     // ... any functions or values to be exposed
   }
}) ()  // these brackets execute the code in the namespace

 

MySheet class

This class abstracts a spreadsheet, and there will be one for each sheet the App is working with. The point here is to cache (in memory, not CacheService) the content of whatever gets read or written to the sheet.

class MySheet {
  static open ({sheetId, sheetName, create}) {
    const ss = SpreadsheetApp.openById(sheetId)
    return ss.getSheetByName(sheetName) || (create && ss.insertSheet(sheetName))
  }
  static replaceValues ({sheet, values}) {
    sheet.getDataRange().clear()
    if(values.length && values[0] && values[0].length) {
      const range = sheet.getRange(1,1,values.length,values[0].length)
      return range.setValues(values)
    }

  }
  constructor (options) {
    this._sheet = this.constructor.open(options)
  }
  get sheet () {
    return this._sheet
  }
  get dataRange () {
    return this.sheet.getDataRange()
  }
  // get currently cached valued
  get values () {
    // if we dont have any then get some
    return this._values || this.getValues()
  }
  // get values from sheet
  getValues () {
    // caching for later
    this._values = this.dataRange.getValues()
    return this._values
  }
  // set currently cached values
  set values (val) {
    this._values = val
  }
  // write current (or new) values to sheet
  setValues ({values}) {
    if (values) {
      this.values = values
    }
    return this.constructor.replaceValues({sheet: this.sheet, values: this.values})
  }
}

static methods

Normally, methods in a class have access to the data of the instance of the class (this…), but occasionally you might want to expose useful methods outside the class that can be used without creating an instance of the class. These static methods have no access to the data of the instance (because there isn’t one), but can be accessed in a standalone way externally. Conversely, they cannot be accessed inside an instance with something like this.staticmethod, since they don’t actually exist in created instances. However, they can be accessed using this.constructor.method as above.

Enhancing the App controller (1)

The first couple of lines of App will instantiate mySheet, and get the data from the airport’s sheet

  // open the airports sheet
  const mySheet = new MySheet({...Settings.sheets.airports})
  // show a couple of rows
  console.log(mySheet.values.slice(0,2))

Here’s the first couple of rows

[ [ 'name',
    'latitude_deg',
    'longitude_deg',
    'elevation_ft',
    'iso_country',
    'municipality',
    'scheduled_service',
    'iata_code' ],
  [ 'Port Moresby Jacksons International Airport',
    -9.44338035583496,
    147.220001220703,
    146,
    'PG',
    'Port Moresby',
    'yes',
    'POM' ] ]

Shob class

It’s not very convenient working with arrays of values, so we’ll use this class to interact with the sheet data. Each MySheet is abstracted into an array of JSON objects, using this class, which is also going to be the interface to MySheet.

// create and manage an object from sheet values
class Shob {
  
  static makeValues ({data}) {
    // derive the headers from the data 
    const headers = Object.keys(data.reduce((p,row)=> {
      Object.keys(row).forEach(col=>p[col]=col)
      return p
    },{}))
    // combine the headers and the values
    return [headers].concat(data.map(row=>headers.map(col=>row[col])))
  }

  static makeData ({values}) {
    const [headers,...data] = values
    return {
      data: data.map(row=>headers.reduce((p,c,i)=>{
        p = row[i]
        return p
      },{})),
      headers 
    }
  }

  constructor ({mySheet}) {
    this._mySheet = mySheet
    this.readValues()
  }

  // convert data to values and store
  setValues ({data}) {
    this.values =  this.constructor.makeValues({data: data || this.data})
  }
  set values (values) {
    this._values = values
  }
  get values () {
    return this._values
  }

  set headers (headers) {
    this._headers = headers
  }
  get headers () {
    return this._headers
  }

  // convert values to data and store
  setData ({values}) {
    const {headers, data} = this.constructor.makeData({values: values || this.values })
    this.headers = headers
    this.data = data
    return this.data
  }

  set data (data) {
    this._data = data
  }
  get data () {
    return this._data
  }

  get mySheet () {
    return this._mySheet 
  }

  writeData (options) {
    // convert data to values and write to sheet
    const data = (options && options.data) || this.data
    this.setValues ({data})
    this.writeValues ()
  }

  writeValues (options) {
    const values = (options && options.values) || this.values
    this.mySheet.setValues({values})
  }

  readValues () {
    this.values = this.mySheet.getValues()
    this.setData ({values: this.values})
    return this.values
  }

}

Enhancing the App controller (2)

The App now looks like this –

const App = () => {
  // open the airports sheet
  const mySheet = new MySheet({...Settings.sheets.airports})
  // show a couple of rows
  console.log(mySheet.values.slice(0,2))
  // convert sheet to object
  const shob = new Shob({mySheet})
  // show a couple of converted rows
  console.log(JSON.stringify(shob.data.slice(0,2)))

and again showing a couple of rows, now converted to JSON objects

	[{
		"name": "Port Moresby Jacksons International Airport",
		"latitude_deg": -9.44338035583496,
		"longitude_deg": 147.220001220703,
		"elevation_ft": 146,
		"iso_country": "PG",
		"municipality": "Port Moresby",
		"scheduled_service": "yes",
		"iata_code": "POM"
	}, {
		"name": "Keflavik International Airport",
		"latitude_deg": 63.9850006103515,
		"longitude_deg": -22.6056003570556,
		"elevation_ft": 171,
		"iso_country": "IS",
		"municipality": "Reykjav��k",
		"scheduled_service": "yes",
		"iata_code": "KEF"
	}]

Now we can start playing with the data – finding airports that are above 3000 metres and logging the result

  // find all the rows with airports at altitude > 3000 metres
  const highAirports = shob.data.filter(f=>f.elevation_ft * 0.3048 > 3000)
  // the only one is cusco in peru
  /**
   * [ { name: 'Alejandro Velasco Astete International Airport',
    latitude_deg: -13.5356998444,
    longitude_deg: -71.9387969971,
    elevation_ft: 10860,
    iso_country: 'PE',
    municipality: 'Cusco',
    scheduled_service: 'yes',
    iata_code: 'CUZ' } ]
   */
  console.log(highAirports)

Now, select all airports with height above 2000 metres and write to a seperate sheet. We’ll also add a new column, which has the height in metres rather than feet, since we prefer to work in metres.

// get a handle to another sheet (and create it if it doesnt exist)
  const metreShob = new Shob({mySheet: new MySheet({...Settings.sheets.metres})})
  
  // replace current values (if any) with amended data from the other sheet
  // write out new values with metres and only airports > 2000 metres
  metreShob.writeData({data: shob.data.map(row=>({
    ...row,
    elevation_metres:  row.elevation_ft * 0.3048
  })).filter(f=>f.elevation_metres>2000)})
  // what did we write ?
  console.log(metreShob.values.slice(0,2))

Sunset namespace

We want to enrich the data in the sheet  with the high airports with data about sunset and sunrise, using an API that returns that given a latitude and longitude. Here’s the namespace for that

const Sunset = (()=>{
  
  const fetcher = ({lat, lon}) => {
    const {getUrl} = Settings.apis.sunset
    const result = UrlFetchApp.fetch(getUrl(lat, lon))
    return JSON.parse(result.getContentText())
  }
  return {
    fetcher
  }
})()

Enhancing the App controller (3)

  // lets add the sunset/sunrise for today
  metreShob.data.forEach(f=>{
    const result = Sunset.fetcher({lat: f.latitude_deg, lon: f.longitude_deg})
    // add some things from the weather
    f.date = new Date()
    f.sunrise = result.results.sunrise
    f.sunset = result.results.sunset
    f.dayLength = result.results.day_length
    console.log(f)
  })
  // and write it back
  metreShob.writeData()

And the final result in the new sheet is

Final app

Here’s the whole thing, with the logging removed

const App = () => {
  // open the airports sheet
  const mySheet = new MySheet({...Settings.sheets.airports})

  // convert sheet to object
  const shob = new Shob({mySheet})

  // get a handle to another sheet (and create it if it doesnt exist)
  const metreShob = new Shob({mySheet: new MySheet({...Settings.sheets.metres})})
  
  // replace current values (if any) with amended data from the other sheet
  // write out new values with metres and only airports > 2000 metres
  metreShob.writeData({data: shob.data.map(row=>({
    ...row,
    elevation_metres:  row.elevation_ft * 0.3048
  })).filter(f=>f.elevation_metres>2000)})
  
  // lets add the sunset/sunrise for today
  metreShob.data.forEach(f=>{
    const result = Sunset.fetcher({lat: f.latitude_deg, lon: f.longitude_deg})
    // add some things from the weather
    f.date = new Date()
    f.sunrise = result.results.sunrise
    f.sunset = result.results.sunset
    f.dayLength = result.results.day_length
  })
  // and write it back
  metreShob.writeData()
}

Libraries

Note that this doesn’t all work with libraries at the time of writing. If you want to expose a class from a library, you’ll need to make a factory function (using the old function syntax)

class yourClass {
 ....
}
// to enable use of classes from a library
function newYourClass (...args) {
  return new yourClass (...args)
}

/// and in your main app - access it like this
const yourInstance = yourLibrary.newYourClass(arg1,arg2)

Similarly, v8 has a problem where namespaces defined with const or arrow functions are not visible in from libraries. Here’s a workaround

const Settings = (() => {
  return {
   ... whatever
  }
})()
// to make this visible when a library
var LibSettings = Settings

// and in your main app - access it like this
const settings = yourLibrary.LibSettings

 

Summary

This example does not rely on order, all the files can be separate or combined in any order, and global space pollution is minimized. Each of the scripts is reusable so can easily be put in a library or copied by other projects. My golden rules are

  • Nothing executable should be in global space
  • Don’t rely on the order that things are processed
  • Minimize the number of executable functions (1 is good)
  • Always assume your code will be reused somewhere else.