Search code examples
google-apps-scriptgoogle-sheetslibraries

I need to make the end user able to run some functions without google apps script without giving him access to the code

I have some google sheets that should have some functions where the ordinary user should be able to run them but I don't want them to able to see the code, so what I am doing now is:

  1. creating a sheet with a bound script that references a library that contains the code
  2. a library containing the code that can be run from the scripts Now the problem is I have to give users access to the library for them to able to run the functions in it from the sheet which makes the whole idea useless because I don't want them to see my codes. if anyone have any idea how I can hide my code but also give users permissions to run functions in the code please help, thank you.
Solution

  • I believe your goal is as follows.

    1. From your following title

      I need to make the end user able to run some functions without google apps script without giving him access to the code

      • You want to make users run Google Apps Script without both authorizing the scopes and showing the script to the users.

    In this case, for example, as a workaround, how about using Web Apps? I thought that when Web Apps is used, your goal might be achieved. The flow of this workaround is as follows.

    Usage:

    1. Create a new project of Google Apps Script.

    Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.

    If you want to directly create it, please access https://script.new/. In this case, if you are not logged in to Google, the log-in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

    2. Sample script.

    Please copy and paste the following script to the created Google Apps Script project and save it. This script is used for Web Apps.

    function doGet(e) {
  const {value} = e.parameter;
  
  // do something: Please put the script you want to run.

  return ContentService.createTextOutput(`<result>Updated ${value}</result>`).setMimeType(ContentService.MimeType.XML); // Here, you can return the value.
}

    This script is a sample script. Even when you directly use this, you can test this workaround.

    3. Deploy Web Apps.

    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 your situation, I thought that this setting might be suitable.
    6. Please click "Deploy" button.
    7. Copy the URL of the Web App. It's like https://script.google.com/macros/s/###/exec.

    4. Testing.

    In order to test this Web Apps, please put your Web Apps URL, the inputted value, and the formula as follows.

    enter image description here

    In this test, the sample formula is =IMPORTXML(CONCATENATE(A1,"?value=",A2),"/result").

    By this flow, the user can run your script without both authorizing the scopes and showing your script. In this workaround, the value is returned as XML data. By this, Web Apps can be used with IMPORTXML.

    Note:

    • 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: