CODEWITHSUNDEEP
google-apps-script
Arie Osdorp
Arie Osdorp

Reputation: 732

google-apps-script how to avoid repeating code?

I have 10 different google sheets. I have a very basic google apps script to apply to all of these. This is the script:

function myRecalculate() { 

var ss = SpreadsheetApp.openById('sheet ID');
var sheet = ss.getSheetByName("Sheet7"); 
var cell = sheet.getRange('A1') 
var number = Math.floor(Math.random() * 8) + 1;
cell.setValue(number) 

}

And I want this to be used in 9 other sheets as well. Is there a way in GAS to avoid repeating this code 10 times? I could just copy paste and alter the sheet ID every time, but does not feel right.

Is there a simple solution?

edit. Solved it myself with packing all sheetID's in an array, and loop the function through the array:

function myRecalculate() { 

var idBE = "id of spreadsheet1"
var idDE = "id of spreadsheet2"
var idFR = "id of spreadsheet3"

// pack them in array
var id = [idBE, idDE, idFR]

// loop through array, do something with sheet
for (i in id){
var ss = SpreadsheetApp.openById(id[i])

var sheet = ss.getSheetByName("Sheet7"); 
var cell = sheet.getRange('A1') 
var number = Math.floor(Math.random() * 8) + 1;
cell.setValue(number) 
}}

Upvotes: 0

Views: 188

Answers (1)

Douglas Gaskell
Douglas Gaskell

Reputation: 10030

You can use libraries!

Here is the reference for Apps Script libraries: Libraries

Note: Code ran through libraries is not as fast as code ran directly from your project.

In short, you can create a version of your code by going to File -> Manage Versions. Save a new version. Then go to File -> Project Properties and copy the Project Key.

Go to your next Apps Script project, and go to Resources -> Libraries. Paste your project key into the Find a Library text field and click Select. Select the version of you're library that you want to use and hit save.

You can now reference the code in that Apps Script project like: MyProject.myRecalculate()

Upvotes: 2

Related Questions