Giving access to a Google Spreadsheet and its accompanying Apps Script-CodePudding

I have a spreadsheet I'm using to manage a bunch of content, with a script I've written that adds an "Export" button to the menu. When the button is clicked, the script gets all the appropriate data and formats it all in a specific way. The formatted version is saved to my Google Drive with a timestamp but a download link is also provided. I'll include a simplified version of the script below in case modifications are required.

I rarely ever use Google's Apps Scripts so I'm rather unfamiliar with the ins and outs of it. I only know the basics (how to write a script that can run when something is done from the spreadsheet's page).

I'm aware I can invite a user to my spreadsheet (or just make it public) but that doesn't seem to bring the script along with it. The script and all the formatting that's being done is the main part of what the person I'm inviting needs. I'm aware that for file.getDownloadUrl() to work (assuming the file is still saving on my Drive), I'd need to give the individual access to that folder as well which isn't a problem.

The question is, how do I give them access to the script so they get the Export menu item? Am I not able to? Am I basically limited to creating a button with the export function bound to it?

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var csvMenuEntries = [
    {
      name: "Export as CSV",
      functionName: "csvExport"
    },
    {
      name: "Export for wiki",
      functionName: "wikiExport"
    }
  ]
  ss.addMenu("Export", csvMenuEntries)
}

function prepare(type) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ssName = ss.getName()
  const sheet = ss.getSheets()[0]
  const sheetName = sheet.getSheetName()
  
  const folderName = ssName   ' exports'
  let folder
  try {
    folder = DriveApp.getFoldersByName(folderName).next()
  } catch (err) {
    folder = DriveApp.createFolder(folderName)
  }
  let fileName
  if (type) {
    const extension = type === 'csv' ? 'csv' : 'txt'
    fileName = ssName   '_'   sheetName   `_${type}_`   new Date().getTime()   `.${extension}`
  }

  return { ss, ssName, sheet, sheetName, folder, fileName }
}

function download(file) {
  const downloadURL = file.getDownloadUrl().slice(0, -8)
  showUrl(downloadURL)
}

function showUrl(downloadURL) {
  var link = HtmlService.createHtmlOutput(`<a href="${downloadURL}">Click here to download</a>`)
  SpreadsheetApp.getUi().showModalDialog(link, 'Your file is ready!')
}

function csvExport() {
  const { ss, sheet, folder, fileName } = prepare('csv')

  const csvSettings = getCsvSettings(ss)
  const csvFile = convertRangeToCsv(sheet, csvSettings) // not going to share this. It's simple but irrelevant
  const file = folder.createFile(fileName, csvFile)

  download(file)
}

function wikiExport() {
  const { sheet, folder, fileName } = prepare('wiki')

  const wikiFile = convertRangeToWikiFormat(sheet) // not going to share this. It's simple but irrelevant
  const file = folder.createFile(fileName, wikiFile)

  download(file)
}

CodePudding user response：

A container-bound script has the same access as its parent spreadsheet, so if you're sharing the spreadsheet you're also sharing the script (though if they have only view access they have to create their own copy to see it):

All container-bound scripts use the same owner, viewer, and editor access list defined for the container file.

With that in mind, there are a few limitations when using scripts. First, they will not trigger for anonymous users (i.e., users that are not signed in), even if the sheet is editable to the public. You'll notice that if you try to open the script editor as anonymous, you will be asked to sign in. There's also a feature request to allow this on Google's issue tracker here.

Secondly, even if the users are signed in, there are other restrictions for Apps Script's triggers:

onOpen(e) runs when a user opens a spreadsheet, document, presentation, or form that the user has permission to edit.

Users need permission to edit the file for the onOpen() trigger to run. If they have viewer or commenter access the menu won't show up. In fact, you'll find that most script functions won't work if the users have only viewer access since they need editor access for most interactions with the sheet.

So if you want this menu to show up you'll need to give your users explicit editor access. If you really must keep your sheet as view-only or want to interact with anonymous users you can consider building a Web App instead and have the users get the download link from there. The web app has ways to communicate with the back-end or the Sheet so you should be able to reproduce your current code that way as well.

References: