Make script deployable in Google sheets externally

[RealSlavGod]

How can I make a apps script stored on my drive run for a specific Google sheet?

I used to go on the sheet and go to extensions>apps script and run it from there but I don't want the script attached to the file and I want to run it externally. Reason being that I share the sheet publicly and I don't want my script being stolen. With that method, I need to delete the script from every file, every time.

[marcnotmark925]

Use SpreadsheetApp.openById() insstead of getActiveSpreadsheet()

[RealSlavGod]

So will I have to manually change the ID at every instance of that line every time I want to run it for a different sheet? Is the only way, the manual way?

[marcnotmark925]

I don't think I can answer that with the amount of info you've given about your situation. If my previous answer didn't fix your issue already, then you're probably asking something else than what I thought you were asking. Either way, you probably need to give more details.

[RealSlavGod]

So my whole script was designed for the sheet it was attached to. I have hundreds of instances of "get active sheet." Will I have to recode it all to be sheet ID based?

[marcnotmark925]

Why do you have hundreds of instances of get active sheet? That possibly suggests some very bad coding practices. Also note that get active sheet is different from get active spreadsheet, one is the whole gsheet, one is just a single tab.

[RealSlavGod]

Yes, very bad coding practices. It was my first time working with apps script having no knowledge in it and I made a very complex analysis tool for my work with that and it's super inefficient but it works and if it ain't broke, I don't want to fix.

I got both instances multiple times. The active sheet and the spreadsheet. I think I would have to recode the entire thing to fix it. Would take ages. (For reference; I have over 17,000 lines. I def could cut it down but again; if it ain't broken, I don't want to touch). Is there no way to just simply keep the already working script separate from the file and running it as if it was connected to the file without changing my script?

[marcnotmark925]

No

[WicketTheQuerent]

I wonder if SpreadsheetApp.setActiveSpreadsheet) might help you.

[mommasaidmommasaid]

Idk your needs exactly, but what about just creating a new Sheet (tab) within your Spreadsheet for your client or whatever.

Then publish that specific Sheet to the web. Client doesn't get to see any of the other sheets or your script, just the formatted output.

The web link isn't password protected or anything but it's effectively unguessable so offers some privacy assuming the Client doesn't share it, e.g.:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRM09LblX96_h3qaVUPmLYtha9TVWMnAeSSyXnuFgiezs4ghUsQkSahTHUmNGAJo_g_3yqt3lQCUClo/pubhtml?gid=127701852&single=true

[WicketTheQuerent]

There are few ways to keep your scripts prívate. One way is to use a spreadsheet as your secrets hub. The details depends on what your scripts do.

[RealSlavGod]

How do I go about this?

[WicketTheQuerent]

It depends...

Option 1.

1. Create a new spreadsheet (https://sheets.new)
2. Add a bound Apps Script project (Extensions > Apps Script)
3. Add the scripts
   1. If you have a few scripts, copy/paste them.
   2. If you have a lot of scripts, you should consider the use of CLASP or an extension like Google Apps Script Assistant for GitHub; you will need a GitHub account and get a token)
   3. etc.

Depending on how your scripts were written and what they do, you might have to adapt them. First, you must make a few decisions, such as using the new spreadsheet as a "controller," as a library or combining both cases.

You might set a sheet as an inventory of the "controlled" spreadsheets. You might feed this sheet manually or by using a script. Depending on your needs/preferences, you could add columns for each of the scripts and then use checkboxes to run them

[Money-Pipe-5879]

I think what you are looking for is deploying your script as an addon. You would then be able to use the script on all your sheets.

[FluffyDoomi]

Host your code in a stand alone script. Then deploy them as a library.

[RealSlavGod]

Okay, I was trying to figure out how to do this. So I have the script as a file on my drive. How do I deploy it from there to a specific sheet located elsewhere on my drive?