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.