3

I have several dozen Google Sheets that I manage for my company that all use the same Apps Scripts library and I am trying to find the easiest way to be able to update the library and have the changes propagated to the Google Sheets. I understand the use Development mode so that the Google Sheets using the library have the most up-to-date code however that requires giving edit privileges of the Apps Scripts project to the Google Sheets users.

The alternative is to turn Development Mode off so that users would only require read privileges however, to then update the code used by the Google Sheets, I would need to save a new version of the Apps Scripts library and then manually update every Google Sheet with the new library version.

Is there anyway to update the library version used by a Google Sheet using one of the Google APIs or some other way that could be done programmatically?

5
  • 4
    You can implement library update by using new Apps Script API: developers.google.com/apps-script/api/how-tos/manage-projects Commented Jan 12, 2018 at 16:12
  • The new Apps Script API can change the code's version number. But you need to change the library version. The documentation states: "Once a version is saved, it can no longer be modified." So, according to the documentation, you can't overwrite an existing version. You can however update the manifest file of an Apps Script file, and the manifest file has a property for the library version. So, I think what you need to do is update the manifest file in the project with the library. Because you are doing this within your company, it should work. Commented Jan 13, 2018 at 2:30
  • 5
    Apps Script documentation for Manifest file The key is to update the manifest file in the project that contains the library. You don't need to use the API to update the library code or the library version number. Commented Jan 13, 2018 at 2:35
  • @AlanWells I'm really interested on your approach. I got that the manifest file keeps the versions of the libraries to be used, but how can I change this on Clients' files? Can you give an example please? Commented Apr 4, 2019 at 14:29
  • I don't have an example. Sorry. I've used the API to overwrite one project with another, but not change the manifest. Commented Apr 4, 2019 at 14:36

2 Answers 2

1

Sorry for answering after 1 year, If i understand, you have a Script Project which is a library for several users project and your problem is to deploy a livrary version in every client project.

As Alan Wells says, you just have to replace the manifest of each client project.

Your manifest is a appsscript.json file. (you could show/hide the manifest file in a project)

Manifest file syntax :

{
  "timeZone": "XXX",
  "dependencies": {
         **** SOME dependencies you need for the client**** 
       ,
    "libraries": [{
      "userSymbol": "MAIN_SCRIPT_NAME",
      "libraryId": "MAIN_SCRIPT_ID",
      "version": "MAIN_SCRIPT_VERSION"
    }]
  },
  "exceptionLogging": "STACKDRIVER"
}

You could use CLASP to manage your script easily.

In your example, in clasp, you create a master folder for your project and a subfolder for every Clients Script. You need to sync every subfolder to his GoogleAppsScript. The initialisation could be long and boring, but with a little shell you be able to push the manifest to every client script with on click/script..

You just have to modify the manifest into your Master Folder and run a script like :

for x in `ls -d SubFolder*`; do (cp appsscript.json $x ; cd $x; clasp push -f >/dev/null ; cd ..; echo "Update done on "$x) ; done

Now you can update the manifest for every client, Library version is available, but the dependencies too... (if you need start some new, or end others)

Sign up to request clarification or add additional context in comments.

Comments

1

You can update the version with a trigger on the client-side or using a webapp with the ScriptSync library, something like this:

function updateLibraryVersion(libraryId='', libraryName='', version) {
  const script_id = ScriptApp.getScriptId();
  const current_script_json = ScriptSync.getScriptContent(script_id);
  var appsscript_json = current_script_json.files.find(file => {
      return file.name === "appsscript"
  });

  if(appsscript_json) {
    appsscript_json = JSON.parse(appsscript_json.source);
    var library = appsscript_json.dependencies?.libraries?.find(lib => {
        return (
            lib.libraryId === libraryId
            || lib.userSymbol === libraryName
        );
    });

    if(library) {
      let new_version = version || Number(library.version) + 1;
      library.version = (new_version).toString();
      appsscript_json.source = JSON.stringify(appsscript_json, null, 2);
      const updater = ScriptSync.assignTemplate();
      updater.addFileToUserJson("appsscript", appsscript_json);
      updater.viewChanges(370);
      Utilities.sleep(20000);   // waiting for interruption by user (if needed)
      return updater.commit();  // after, apply the changes
    }
  }
  return false;
}

function do(e) {
  // ....
  const libraryId    = "your_library_script_id";
  const libraryName  = "LibraryNameUsersDefined";
  const updateResult = updateLibraryVersion(libraryId, libraryName, e.version);
  console.log(updateResult);
  //...
}

If using a trigger:

function setTriggers() {
  ScriptApp.newTrigger("t_updateVersion").timeBased()
    .everyDays(1)
    .atHour(1).create();
}

function t_updateVersion() {
  /* Initialize your template script */
  const template_script_id = "your_template_script_id";
  const updater = ScriptSync.assignTemplate(template_script_id);

  // template_appsscript - any file in your template (representing appsscript)
  // script_appsscript - appsscript in the current user's script (use 'appsscript' file this)
  const match = updater.compareFilesByContent(template_appsscript, script_appsscript);

  if (!match) {
    const updateResult = updateLibraryVersion(libraryId, libraryName);
    // or copy the whole file (not the best idea, but as an option)
    // because the user could set his own code here
    const fileToCopy = 'appsscript';
    updater.AddNewFile("template_appsscript", "appsscript", "json");
  }
}

Also, in the best way, using these examples, you can extract a new version of the library from the template file and set it to the user script "appsscript" file. Not tested (check it first):

/**
 * ScriptSync id (from hint while text 'updater'): 
 * @param {MRaUrgsx725qu8gUzKJlDXtz8VMKMxn5x.ScriptSync}  updater
 */
function updateLibraryVersionUpdater(updater, libraryId='', libraryName='', version) {
  const script_id = ScriptApp.getScriptId();
  const current_script_json = ScriptSync.getScriptContent(script_id);
  var appsscript_json = current_script_json.files.find(file => {
      return file.name === "appsscript"
  });

  if(appsscript_json) {
    appsscript_json = JSON.parse(appsscript_json.source);
    var library = appsscript_json.dependencies?.libraries?.find(lib => {
        return (
            lib.libraryId === libraryId
            || lib.userSymbol === libraryName
        );
    });

    if(library) {
      library.version = version;
      appsscript_json.source = JSON.stringify(appsscript_json, null, 2);
      updater.addFileToUserJson("appsscript", appsscript_json);
      return updater.commit();  // apply the changes
    }
  }
  return false;
}

/**
 * ScriptSync id: 
 * @param {MRaUrgsx725qu8gUzKJlDXtz8VMKMxn5x.ScriptSync}  updater
 */
function getLibraryVersion(updater, libraryId, libraryName) {
  const template_app_json = updater.getFileFromTemplate("appsscript", sourceOnly=true);
  if (template_app_json) {
    template_app_json = JSON.parse(template_app_json);
    var library = template_app_json.dependencies?.libraries?.find(lib => {
        return (
            lib.libraryId === libraryId
            || lib.userSymbol === libraryName
        );
    });
    return library.version;
  }
}

function t_updateVersion() {
  var updateResult;
  /* Initialize your template script and library */
  const template_script_id = "your_template_script_id";
  const updater = ScriptSync.assignTemplate(template_script_id);

  // template_appsscript - any file in your template (representing appsscript)
  // script_appsscript - appsscript in the current user's script (use 'appsscript' file this)
  const match = updater.compareFilesByContent(template_appsscript, script_appsscript);

  if (!match) {
    const libraryVersion = getLibraryVersion(updater, libraryId, libraryName);
    if (libraryVersion)
        updateResult = updateLibraryVersionUpdater(updater, libraryId, libraryName, libraryVersion);
  }

  return updateResult || false;
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.