Share App Script Web App Between Multiple Google Sheets

Question

I have written a web app using app script linked to a Google Sheet. I would like to distribute this script to multiple users, but I would like for each user to have an instanced version of the sheet.

The script is authenticating users with OAuth to a third party API and returning data based on that user's permission level, thus why the sheets need to be unique to the user, but using the same script.

Is there a way to do this?

I edited my answer to include something that might be more helpful for you in the long run — Passer-By

Passer-By Passer-By · Accepted Answer · 2019-08-14T19:47:54

If you go to the Google Apps Script Dashboard you're able to create a standalone script. After pasting your code in there, if you go to File > Manage Versions you'll be greeted with a prompt to enter a description and a button that says Save new version. After entering the description you'd like, go ahead and click the button.

Now you'll want to copy this part of the url of your script (which is your script ID) and in the desired spreadsheets, in the script editor, select Resources > Libraries and enter your link into this box

After adding it, select your version (I didn't enter a description for this one, however if you do enter a description when making your project version, it will appear next to the version number).

Hit save and now you'll be able to access functions from the library you've created like so

You'll know it's working properly if it brings up suggestions after you type the period like this

Edit

I just realized you mentioned it's a web app, in which case there's a different way you can do this - you'll still want it as a standalone script, but what you can do is to pass a sheet ID based on the user accessing the web app. I personally don't have experience with OAuth, however I have connected a single web-app to different sheets depending on a drop down before.

How I accomplished this is as such:

I first set the value of the drop down in the html to include the sheet ID I wanted to send it to (changed a few things since this is from company code)

<option value="1THvC8JhF-LxZOYBGRaIvpZpYoNtyfOuteWNPndtrCQo@[Title of the page]">[Option to select]</option>

Then in the Javascript I set the url to include the title and ID when it redirects to the next page

function boxVal(url){
  var ID = document.getElementById('[ID of select]').value;
  var strArr = ID.split('@');
  ID = strArr[0];
  var title = strArr[1];
  if(ID === "Select Your Department"){
    url += "?page=Department Select&title=" + title;
  }
  else{
    url += "?page=Main Page&title=" + title + "&id=" + ID;
  }
  document.getElementById('link').href = url;
  document.getElementById('link').click();
}

(for reference as to how I have multiple pages if you don't already know, see this post)

Then in the doGet() function I grabbed the id paramater that I passed to the URL that I could then pass to my functions in the javascript

var qString = e.parameter['id'];
uProps('SPREADSHEET_ID', qString);

where uProps is

function uProps(prop, propVal){
  var userProperties = PropertiesService.getUserProperties();
  userProperties.setProperty(prop ,propVal);
}

Which creates a user property that can be referenced after the page changes.

Then back to the javascript connected to the html file I use this to pass the user properties to other functions

function passFunction(receiver){
  switch(receiver) {
    case 1: google.script.run.withSuccessHandler(add).getProps('SPREADSHEET_ID'); break;
    case 2: google.script.run.withSuccessHandler(getTopInterrupt).getProps('SPREADSHEET_ID'); break;
  }
}

where passFunction executes from an onclick in the html

This is the .getProps function which is in the Code.gs file (the one I have containing the doGet())

function getProps(prop){
  return PropertiesService.getUserProperties().getProperty(prop);
}

I think if you can get this to work with your OAuth system, then this would be the best route to go, as you could have a single version of the web app serving all your users without having to put any code in the other sheets.

Hope this helps

Share App Script Web App Between Multiple Google Sheets

1 Answers

Edit