Is there a way to duplicate an action button from one Google sheets file to another?-CodePudding

TL;DR: I'm looking for a way to add an action button using an existing google-script to all future-added files in a Drive folder.

Hi all,
I have a Google-apps script that checks for a change in files count in a Google Drive folder, and upon change applies a certain script to it (all the scripts are in the same project and all files are in the same format).
I've decided I want to add an action button to one of the sheets to apply a specific filter upon users' requests.

I'm looking for a way to add this action button to all of the future-added files. I've thought of the next possible implementations:

Create a drawing using the script - I couldn't manage to find a method that generates a new drawing.
Make a specific range clickable - Also couldn't find a way to do that
Add a menu to the file's toolbar - I get an exception thrown that SpreadsheetApp.newMenu() can't be called from the current context.

Did anybody implement something similar and can help?
Thanks!

CodePudding user response：

As for the Menu, you can try this:

function onOpen() { //[OPTIONAL] Created a custom menu "Timestamp" on your Spreadsheet, where you can run the script
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Whatever you want it to be called') //keep the ''
      .addItem('Function Description', 'function call') //for the function, don't use ()
      .addSubMenu(ui.createMenu('submenu name') //only if needed
        .addItem('Function Description','function call')
        .addSeparator() //aestethics only
        .addItem('Function Description','function call')
    .addToUi();
}

CodePudding user response：

The Instant Tool Bar

Here's a couple of functions that allow you to build a sidebar that provides you with instant access to a variety of html controls including buttons which can call any server side function via google.script.run.

The first function launches the sidebar which provides instant access to a variety of tools which can be created with html.

function launchSideBarButtons() {
  const ss = SpreadsheetApp.getActive();
  let html = '<html><head><style>input {margin: 2px 5px 1px 0;}</style></head><body>';
  html  = '<input type="text" id="txt1" placeholder="Enter Folder Id" value="folderid" />';//Change the folderid to the default folder that you wish to check on enter a new folder id at the time just prior to execution
  html  = '<br><input type= "text" id="txt2" placeholder="Display Number Files" readonly />';
  html  = '<br><input type="button" value="Check Folder" onClick="checkFolder();" />';
  html  = '<script>';
  html  = 'function checkFolder() { let id=document.getElementById("txt1").value;google.script.run.withSuccessHandler(function(l){document.getElementById("txt2").value=l;}).checkMyFolder(id);}';
  html  = 'console.log("My Code")';
  html  = '</script></body></html>';
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutput(html).setTitle("Instant Tool Bar"));
}

This function is the server side function that actually opens the folder and counts the number of files.

function checkMyFolder(id) {
  const folder = DriveApp.getFolderById(id);
  if (folder) {
    const files = folder.getFiles();
    let n = 0;
    while (files.hasNext()) {
      let file = files.next();
      n  ;
    }
    return n;
  } else {SpreadsheetApp.getUi().alert("Check folder id")}
}

This function creates a installable onMyNewOpen() trigger that will launch the side bar when the spreadsheet opens thus making it unnecessary for you to have to go looking through a menu for a button click. It's setup so that it will only create one trigger no matter how many times you run it.

function onMyNewOpen() {
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "launchSideBarButtons").length == 0) {
    ScriptApp.newTrigger("launchSideBarButtons").forSpreadsheet(SpreadsheetApp.getActive()).onOpen().create();
  }
}

The Dialog: