How to I get a spreadsheet button to run a standalone script as me when the sheet is opened by another user account

Question

I have some code in a standalone script that returns the tasks from one of my task lists.

var tasklistID="mytaslistid"
function getTasks() {
    var tasks=Tasks.Tasks.list(tasklistID)
    return tasks
}

The code works fine when called through the installed onEdit trigger. But the user may need to simply refresh the list of task on the spreadsheet. So I have inserted a button that calls a function in the bounded script which in turn calls the function in the standalone script

function getTasks(){
    TaskManagerScript.getTasks()
}

But this only works when the spreadsheet is opened in my account. When opened in another account it returns the other users task.

I tried to flush the spreadsheet and run the getTasks function in the onOpen trigger but this did not seem to work.

I need assistance to find a workaround.

Answer 1

• Your Spreadsheet is shared with users.
• You want to make users run your script by clicking a button on Spreadsheet.
• You want to make users use your task list.
  • The task list is yours.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Reason of your issue:

When users run your script on Spreadsheet by clicking a button on the sheet, the script is run as each user. By this, users cannot find mytasklistid of var tasklistID="mytasklistid" from user's task list. I think that the reason of your issue is this.

Workaround:

As one of several workarounds, how about using Web Apps? This is also mentioned by TheMaster. When Web Apps is deployed as Execute the app as: Me, even if other users run the script of Web Apps, the script is run as owner of Web Apps (you). Under this situation, when users run the script by clicking a button, users request to Web Apps you deployed using UrlFetchApp. By this, users can use your task list ID. This workaround uses this situation. The flow of this workaround is as follows.

1. Deploy the script as Web Apps.
   • Set Execute the app as: as Me.
   • Set Who has access to the app: as Anyone, even anonymous. This setting is for testing.
2. Users use the deployed Web Apps like the external API.

By this, users can use your task list.

Sample script:

The sample script is as follows. There are 2 sample scripts for Server side (your script) and Client side (user's script), respectively. You can also put both scripts in the same project.

1. Please copy and paste "Server side" to your project and deploy it as Web Apps.
   • About how to deploy Web Apps, you can see at here.
2. Please copy and paste "Client side" to user's project (the bound script of Spreadsheet including the button). At this time, please set the URL of Web Apps like https://script.google.com/macros/s/###/exec.

Server side: your script

function doGet() {
  var tasks = getTasks();
  return ContentService.createTextOutput(JSON.stringify(tasks)).setMimeType(ContentService.MimeType.JSON);
}

function getTasks() {
  var tasklistID="mytasklistid";
  var tasks=Tasks.Tasks.list(tasklistID);
  return tasks;
}

Client side: user's script

Please assign this function to the button.

function getTask(){
  var url = "https://script.google.com/macros/s/###/exec";
  var tasks = UrlFetchApp.fetch(url);
  Logger.log(tasks)
}

Note:

• When you modify your script of Web Apps, please redeploy as new version. By this, the latest script is reflected to Web Apps. This is important point for using Web Apps.
• In this workaround, the library and client are separated. Users as the client retrieve the data from Web Apps.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script

If I misunderstood your question and this was not the result you want, I apologize.

Answer 2

Consider setting up getTasks() to run automatically on a time-based trigger which will execute as you.

Edit menu (in standalone script) > Current Project's Triggers > Add Trigger button

This eliminates the need for a refresh button or web app deployment if the frequency of that time-based trigger is suffice.