How to run a separate file script in GAS

GAS
この記事は約10分で読めます。

I wanted to execute a script of another file with GAS, so I thought about the method.

I’m not thinking about it as a best practice, so don’t worry. Maybe there is a method to hit the execution API locally besides the method introduced, but this time I will explain assuming that GAS of another file is executed with GAS.

Advertisements

Thing you want to do

The overall image is like this. Execute only GAS in the spreadsheet to execute collectively and aggregate the results, execute the container binding script of execution targets 1 and 2, and collectively copy the data displayed on the sheet to the sheet of the execution SS I feel like doing it.

For the last copy part, the importRange function is also sufficient. It’s not the main this time, so I made it easily with GAS.

What are the useful cases?

I think that it will work effectively in the following cases.

  • Irregular timing to execute script of execution target file (trigger cannot handle)
  • When you want to execute at once by linking
  • I want to put the data storage on a separate sheet and realize a fractal data structure (in a separation environment).

Of course, it is possible to open the sheet and execute the script manually by doing the same thing, but it will be troublesome if the number increases, so I think there are cases where it is better to use this method.

If you have 20 or 30 target SpreadSheets, just opening and executing each one is quite troublesome. ..

How to execute another file GAS with GAS ~ Implementation method ~

You can do it in the following steps. The point is to make a GAS for each execution target into a library and use it as a library on the sheet side to be executed collectively

  1. Create a script for the sheet to be executed
  2. Import a library in summary sheet
  3. Call execution function from Identifier

Please refer to the following article for how to create and use the library

【How to create a library】

【How to use (import) the library】

Reference script and execution result

Now let’s look at the reference script and the execution result.

Script introduction

【Execute collectively SpreadSheet script】

function aggregation() {
//Script to run GAS in another environment with GAS
  target1.setvalue_test()  //Execute the script of the sheet of execution environment 1
  target2.setvalue_test() //Execute the script of the sheet of execution environment 2
  
//From here, a script to simply get the results of execution on the sheets of execution environment 1 and 2 and copy them all to the execution SS
  let sheet1 = SpreadsheetApp.openById("1TmoYLWsPfbfkuQeOFuEyhX7MyMalU-dGE2LVVLCRo_4").getSheetByName("シート1");
  let sheet2 = SpreadsheetApp.openById("1DgJ-XOiSLmAwb41Sjq8Ndde6hLPRkaC7BjJ72DRE8xc").getSheetByName("シート1");
  
  let targetsheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("対象1");
  let targetsheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("対象2");
  
  importData(sheet1, targetsheet1)
  importData(sheet2, targetsheet2)
}

function importData(org_sheet, target_sheet){
  let data = org_sheet.getDataRange().getValues();
  target_sheet.getRange(1,1,data.length, data[0].length).setValues(data)
}

【Script of Execution environment 1】

function setvalue_test() {
  let sheet = SpreadsheetApp.openById("Sheet ID of execution environment 1").getSheetByName("シート1");
  let data = "実行環境1";
  let row= 1;
  let column =1;
  
  sheet.getRange(row, column).setValue(data);
}

【Script of Execution environment 2】

function setvalue_test() {
  let sheet = SpreadsheetApp.openById("Sheet ID of execution environment 2").getSheetByName("シート1");
  let data = "実行環境2";
  let row= 1;
  let column =1;
  
  sheet.getRange(row, column).setValue(data);
}

Execution result introduction

When you confirm the execution target 1, the GAS of the execution target 1 is started and the execution environment 1 is written in the sheet as follows.

before execution

after execution

Execution environment 2 is the same.

For the summary sheet, the data entered in the execution environment 1 has been copied as follows.

before execution

after execution

How to execute GAS of another file with GAS~attention points~

The points to note are the same as when creating a library. Here are some excerpts from the articles introduced in the fourth series related to libraries.

Attention point 1 Active methods

The library is called in the environment of the side to be used. Therefore, the active method (activespreadsheet/ activesheet/ activecell, etc.) will get the active version of the calling environment’s spreadsheet.

https://admin-it.xyz/gas/library-use-ownlibrary-attentions/

The most important thing to note is that if you are using Active methods in the GAS you are running, the behavior will not be as you intended.

Summary

I have introduced the method to run GAS from another file.

Essentially, if you don’t have to use this method, it is the best, and you should design such a business. However, there were some things I couldn’t do by all means, so I tried to use it for the purpose of eliminating the troublesomeness on the GAS side.

It may not be a very hit use case, but it was just a story that you could do this (because the name was introduced as an executable API…).

コメント