How to use Google Apps Script on any/all Spreadsheets?

Question

I am trying to implement a spreadsheet app using Google Apps Script. But I have several issue that does not mach my requirements. I have looked in to these issues and was unsuccessful to find answers or better alternative ways to implement this functionality.

Requirements:

1. Write manageable testable code

2. Not to download the script to each spreadsheet but to somehow when you install once you can use it on any spreadsheet in the drive.

1,2 are the problems I faced.

1. The Google Apps Script IDE seems to have difficulty in handling a large code base (I mean not to keep the code its about managing and handle the growth of the code)

2. This is the major problem (Not matching the requirement)

   Let's say I create a script for a spreadsheet and then I deploy it and then I can then install the script on any other script I like and then execute it. But this has to be done for each and every spreadsheet. The installed script is not in the script manager to be used in all the spreadsheets without installing the script form the gallery every time.

And also When I create a project of Spreadsheets directly on the Google Drive. Since this is not allocated to a particular spreadsheet it does not allow me to select it from the dive (From script manager) and load this script to a spreadsheet I wish, and run/debug it.

I am not hoping to get a better solution to above issue 1.

But issue No. 2 is is the burning one, Is this can be addressed to match the requirement?

If the requirement can not be achieved form Google Apps Script, I have to go for an alternative.

Alternatives I thought so far:

• I thought about desktop application that can give the spreadsheet URL and then give the authentication information and by using the spreadsheet API do the necessary manipulations form the desktop application. This is the dummy est approach which is not so user friendly.

• Go for a Google app, where you can log in using the G mail authentication and then after giving the spreadsheet URL using the Spreadsheet API do the necessary manipulations. But I am not sure about the authentication process for a Google app and the possibility of access permission to the drive.

• Final is the Crome app, Where Crome is provided with the necessary authentication to access a given spreadsheet URL and do the necessary manipulations to the spreadsheet.

Which of those options is preferred, or is there any better solution that matches the requirement I stated above?

Answer 1

The definition of "large code base" is subjective, but even a project with several hundred lines of code can benefit from organization. In the IDE you have two primary tools available to help organize your code; Files and Libraries.

This answer is mainly a collection of links to other answers, within a number of topic areas that should address the concerns you've expressed.

Code Organization & Libraries

A script project can have multiple files, of type "gs" (script) or "html". The namespace extends across all script files in the project. Separating methods into files is a simple way to organize your code within a project (for instance, place all tests in one file separate from production code), although it does very little for reusability across projects.

Libraries are projects that are published to enable other projects to incorporate their capabilities without requiring them to copy the actual code. Have a look at the Notable Script Libraries for a start. Libraries take their name space from the library name - for instance, I have a library of utilities related to input & output of CSV files, which I (cleverly) named CSVUtils. The methods in that library are accessed as CSVUtils.example(), and are supported by the auto-complete feature. Libraries support a limited subset of jsdoc, and when published provide a link to the generated documentation.

Organizing Spreadsheet Code in several *.gs files - even possible?

Is it possible to have one script for multiple spreadsheets?

How, or where, do I define a function for use in more than one spreadsheet?

google-apps-script have multiple scripts in one spreadsheet

Creating a namespace-like organization in a google apps script library

Publishing a Google Apps Script Library

Design patterns for libraries in GAS?

ScriptDb in Library accessed by WebApp

Project and multiple doGet() script files published as web apps -- Need clarification

... every SO question about Apps Script Libraries.

Using external JavaScript libraries:

Start by reading through Understanding Caja Sanitization, especially the section regarding External JavaScript Libraries.

Google Apps Script: How can use js library in HtmlService

Authorization & Ownership

Authorization - Google apps script grant user permissions access services on spreadsheet in ther first run without open script editor

https://stackoverflow.com/questions/15096379/google-app-scripts-how-to-let-shared-users-run-scripts-as-themselves

When does Google require re-authentication?

OAuth in Google Apps Scripts Libraries

Code Transparency

Source-code of Libraries used by WebApps visible to everybody?

How to hide library source code in Google way?