1

I have 2 Spreadsheets, the 1st will Search on the 2nd spreadsheet with data using a google script function.

I want to keep the 2nd spreadsheet with data to be hidden (no editor access) from a user, but he/she will be able to Search on it via the google script function only.

I'm using google script Openbyurl to do it, but it won't let this user to run the Openbyurl unless he/she has editor access to the 2nd spreadsheet.

how should I deal with this?

Below function is in the 1st Spreadsheet, openByUrl links to 2nd Spreadsheet:

function onSearch(SN) { 
var ss = SpreadsheetApp.openByUrl('docs.google.com/spreadsheets/...'); 
var sheets = ss.getSheets(); 

// search for data in ss sheets . . .
// return array of found data

 }
knknkn1995
  • 13
  • 3
  • 1
    Can you provide your current script of `I have 2 Spreadsheets, the 1st will Search on the 2nd spreadsheet with data using a google script function.`? – Tanaike Nov 08 '21 at 05:44
  • https://stackoverflow.com/questions/27462042/can-one-script-trigger-a-function-in-another-script – Kos Nov 08 '21 at 10:24
  • function onSearch(SN) { var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/...'); var sheets = ss.getSheets(); // search for data in ss sheets . . . return arr; } @Tanaike – knknkn1995 Nov 09 '21 at 08:42
  • Thank you for replying. When you show the script, please add it to your question. By this, the readability will be high. By the way, in your script, `arr` is not declared. So, unfortunately, I cannot understand your script. I apologize for this. Can you explain the detail of your script? – Tanaike Nov 09 '21 at 08:51
  • Thank you for updating it. From your updated question, I proposed an answer. Could you please confirm it? If I misunderstood your question and that was not useful, I apologize. – Tanaike Nov 11 '21 at 06:17

1 Answers1

0

I believe your goal is as follows.

  • You have 2 Google Spreadsheets "A" and "B".
  • You want to make the user retrieve values from Spreadsheet "B" with the script of Spreadsheet "A".
  • You don't want to share Spreadsheet "B" while Spreadsheet "A" is shared with the user.

In this case, as a workaround, how about accessing Spreadsheet "B" using Web Apps? By this, you can make the user access Spreadsheet "B" without sharing the Spreadsheet with the user. When this is reflected in your script, it becomes as follows.

Flow:

1. For Spreadsheet "B":

Please copy and paste the following script to the script editor of Spreadsheet "B".

function doGet(e) {
  var SN = e.parameter.sn; // You can use the value of SN here.
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Your 2nd Spreadsheet.
  var sheets = ss.getSheets();

  // search for data in ss sheets . . .
  // return array of found data

  var returnValues = ["sample"]; // Please replace this value for your actual script.

  return ContentService.createTextOutput(JSON.stringify(returnValues));
}

2. Deploy Web Apps.

Please run this flow on the script editor of Spreadsheet "B". The detailed information can be seen at the official document.

  1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
  2. Please click "Select type" -> "Web App".
  3. Please input the information about the Web App in the fields under "Deployment configuration".
  4. Please select "Me" for "Execute as".
    • This is the importance of this workaround.
  5. Please select "Anyone" for "Who has access".
    • In this case, the user is not required to use the access token. So please use this as a test case.
    • Of course, you can also access your Web Apps using the access token. Please check this report.
  6. Please click "Deploy" button.
  7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.

3. For Spreadsheet "A":

Please copy and paste the following script to the script editor of Spreadsheet "A". And, set your Web Apps URL. In this case, please give the value to SN.

function onSearch(SN) {
  var url = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL here.
  var res = UrlFetchApp.fetch(`${url}?sn=${SN}`);
  var ar = JSON.parse(res.getContentText()); // This is the returned value from Spreadsheet "B".
  
  // do something.

}

4. Testing:

When you run the script of onSearch, the value of SN is sent to Spreadsheet "B" and run the script of Spreadsheet "B", and the result values are returned. By this flow, you can retrieve the values from Spreadsheet "B" without sharing Spreadsheet "B" with the user.

Note:

  • In this sample script, when you directly run onSearch, the value of SN is not declared. So please be careful about this.
  • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
  • You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".
  • My proposed script is a simple script. So please modify it for your actual situation.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165