CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Harsh
Harsh

Reputation: 241

Running Script in Other Spreadsheet from Current Spreadsheet

I have two different Spreadsheets with their own script projects. I have created a library from SpreadsheetA and linked the same to SpreadsheetB. Now when I call the function of SpreadsheetA from the script of SpreadsheetB it runs the same in the current active sheet of SpreadsheetB while I want to run the function on selected sheet of SpreadsheetA.

function createDO() {
  var ss = SpreadsheetApp.getActive(); //SpreadsheetB
  var cell = ss.getActiveCell().getRowIndex();
  var spreadSheetAURL = "URL"; //SpreadsheetA
  var spreadSheetsInA = SpreadsheetApp.openByUrl(spreadSheetAURL).getSheetByName("name");
  spreadSheetsInA.activate()
  Sheetname.Project(); //script in SpreadsheetA
};

I need to run the Sheetname.Protect() function for SpreadsheetA from the current SpreadsheetB.

Upvotes: 0

Views: 114

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

Possible Solution:

Firstly, it is worth noting that libraries do not work like this. As Oleg Valter has stated, libraries are by nature utility scripts which are not bound to any one specific script/class/project.

As a workaround, however, you can set up a web app which can be called from one project to another, running code inside a doGet() function on the respective Spreadsheet.

A structure would look something like this:

In Spreadsheet A:

function doGet(e) {
  var sheetName = e.parameter.sheetName
  var sheet = SpreadsheetApp.getActiveSpreadheet.getSheetByName(sheetName);
  sheet.activate();

  // I am not how this works, but I have extrapolated from your question
  Sheetname.Project(); 
}

Then deploy this as a Web App from the Publish > Deploy as web app... menu item. The settings should be Execute the app as: me and Who has access to the app: Anyone (even anonymous).

Then, getting the Web App URL from the dialog, in Spreadsheet B:

function createDO() {
  var ss = SpreadsheetApp.getActive(); //SpreadsheetB
  var cell = ss.getActiveCell().getRowIndex();
  var webAppUrl = "URL"; //SpreadsheetA
  UrlFetchApp.fetch(webAppUrl + "?sheetName=name");
};

The ?sheetName=name parameter is a URL query string which would be passed to the web app's doGet(e)'s event object. This is what is accessed with e.parameter.sheetName.

The string on the right-hand-side of the = in the query string needs to be the sheet name you were trying to activate originally.

References:

Upvotes: 2

Related Questions