cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
hodsagi
Community Participant

Google script based dialog embedded in a Canvas Page

Jump to solution

I am trying to embed this Google spreadsheet in a Canvas page. There is a Google script attached to the spreadsheet, which creates a custom menu item called "Meeting Log", which opens up a dialog. If I open it in using the Google spreadsheet app, I get the following pop up window:

dialog

However, when I embed the spreadsheet into a Canvas page, the dialog opens up completely empty (see screenshot below)

dialog

Could anyone advise me on how to make it work within a Canvas page?

1 Solution

Accepted Solutions
James
Community Champion

 @hodsagi ,

I tried a bunch of things and couldn't get the embed to work properly with the modal dialog. I even used the "File > Publish to Web" feature but that wouldn't include the menus or let the script run at all. I even added a button to the screen that opened the dialog, but there was no text in the modal dialog.

Let me ask another question.

Is there a reason you can't embed a Google Form instead of the Google Sheet?

It looks like the only thing that you really want that doesn't come with Google Forms is the user's email address -- unless you ask for it in the form (or by checking the box to collect addresses). I imagine that you're wanting to automatically detect who the person submitting the form is and save them having to enter their email address. 

I was able to get Google Forms to automatically collect that information. What you do is add a script to your spreadsheet and then go to Edit and add a project trigger to run it when a form is submitted. See (but don't follow exactly) Section 1 steps 2-5 of this tutorial: Tutorial: Automating a Help Desk Workflow

So what I did was modify their script to look at Session.getActiveUser().getEmail(), which is what you had in your script.

function formSubmitReply(e) {
  Logger.log(e);
  Logger.log(Session.getEffectiveUser().getEmail());
  Logger.log(Session.getActiveUser().getEmail());
}

The e variable contains all of the submitted data from the form -- as well as the email address if you check the box to collect it.

The problem is in the other two. In all of my testing, both getActiveUser() and getEffectiveUser() returned my Gmail account as the creator of the script, even if I was logged into Canvas as another user, was in incognito or private mode on the browser, or if I had logged out of my Google account completely. I tried this with my spreadsheet private to me (just using the Form) and with the spreadsheet made public. Nothing I tried made a difference.

In other words, I'm not sure that it's going to do what I think you want it to do.

But if it works for you, then you might be able to do something like what I found in the tutorial and tried.  As part of the formSubmitReply function (which doesn't need to be called that, by the way, especially since you're not replying), you could automatically add the name of the person as a field and do your formatting as part of that function as well. Plus you get the nice look of Google Forms over Google Sheets.

View solution in original post

5 Replies
James
Community Champion

 @hodsagi ,

I tried a bunch of things and couldn't get the embed to work properly with the modal dialog. I even used the "File > Publish to Web" feature but that wouldn't include the menus or let the script run at all. I even added a button to the screen that opened the dialog, but there was no text in the modal dialog.

Let me ask another question.

Is there a reason you can't embed a Google Form instead of the Google Sheet?

It looks like the only thing that you really want that doesn't come with Google Forms is the user's email address -- unless you ask for it in the form (or by checking the box to collect addresses). I imagine that you're wanting to automatically detect who the person submitting the form is and save them having to enter their email address. 

I was able to get Google Forms to automatically collect that information. What you do is add a script to your spreadsheet and then go to Edit and add a project trigger to run it when a form is submitted. See (but don't follow exactly) Section 1 steps 2-5 of this tutorial: Tutorial: Automating a Help Desk Workflow

So what I did was modify their script to look at Session.getActiveUser().getEmail(), which is what you had in your script.

function formSubmitReply(e) {
  Logger.log(e);
  Logger.log(Session.getEffectiveUser().getEmail());
  Logger.log(Session.getActiveUser().getEmail());
}

The e variable contains all of the submitted data from the form -- as well as the email address if you check the box to collect it.

The problem is in the other two. In all of my testing, both getActiveUser() and getEffectiveUser() returned my Gmail account as the creator of the script, even if I was logged into Canvas as another user, was in incognito or private mode on the browser, or if I had logged out of my Google account completely. I tried this with my spreadsheet private to me (just using the Form) and with the spreadsheet made public. Nothing I tried made a difference.

In other words, I'm not sure that it's going to do what I think you want it to do.

But if it works for you, then you might be able to do something like what I found in the tutorial and tried.  As part of the formSubmitReply function (which doesn't need to be called that, by the way, especially since you're not replying), you could automatically add the name of the person as a field and do your formatting as part of that function as well. Plus you get the nice look of Google Forms over Google Sheets.

View solution in original post

hodsagi
Community Participant

Hi James,

Many thanks for looking into this and for your suggestions! I was also thinking about Google Forms but eventually dropped the idea because I want users to be able to see their previous submissions. Now that you brought it up I gave it a second thought and it seems possible to resolve this by embedding the responses spreadsheet in a Canvas page. The only issue is then that I need to generate a few hundred copies of this, which I normally do in with a Google script as well. I will check whether Google forms could be copied and shared in scripts the same way spreadsheets can.

Thanks a lot again!

Janos

James
Community Champion

What I would do is make the form available to those who need to enter the data and make the associated spreadsheet where the results are stored available to just those that need that portion.

You do not need to give edit permission to the form for people to use it, they just need to be able to access it to fill it out.

hodsagi
Community Participant

Right, so you are proposing I could use a single form for all users and sort out their responses? It's a great idea, the only part not clear to me is how to "make the associated spreadsheet where the results are stored available to just those that need that portion". Just to give you a bit of a context, each mentor keeps a mentoring log and they should only have access to their own log entries. If I have a single form in which all mentors enter data, I should be able to share only parts of the associated spreadsheet with them. I'm not sure how to do this.

James
Community Champion

I didn't understand where you were coming from and thought there was one group of people who were entering data for another group of people to process.

Does anyone other than the mentor need access to the information? If not, then each person could have their own form/spreadsheet. But I'm guessing the answer is that other people need access to it as well.

Do the mentors need to update the information in the spreadsheet as well or just enter it once?

How were you handling this issue with just a Google sheet? Knowing that might direct us to a solution.

You can't share just a single tab from a spreadsheet, but if you could extract the information to separate tabs, you can use the use the ImportRange function to pull information from one spreadsheet into another spreadsheet. The problem there is getting the information to go both directions if necessary.

There is also the issue of security as the person using the ImportRange would need permission to access the master spreadsheet, meaning they could open it directly. The code could run as the administrator (the person who needs access to everything), but then it wouldn't get updated unless the administrator ran the code.

A possible work-around might be to have a single master form and master spreadsheet that the administrator has access to. There is a separate spreadsheet for each mentor and the mentor and the administrator have access to this one. When the form is filled out, the information is stored in the master form and the code that is triggered when a row is inserted from a form updates the looks for when a row is inserted into the table to make a call that writes the corresponding line to the second spreadsheet for just the mentor.

If you need the information to be updated in the master spreadsheet, then it would contain code that would go out and poll the individual spreadsheets and look for changes and update the master copy. That would be ran on-demand by the administrator. There may be a need for a "push" or "update" command to send any changes back to the individual mentor spreadsheets.

But the more that you share about what you're wanting to do, I'm wondering if a different system would be better rather than trying to re-invent the wheel with Google apps? All of this is being done external to Canvas and so perhaps using an LTI that would accept the user information from Canvas and then do its own thing might be easier.

Regardless, knowing the workflow and the context helps, so thanks for sharing.  

I asked the other questions because it's usually easier to give a possible solution when you know what the problem that you're trying to solve is. In some ways, this is similar to the five whys method that Toyota uses. Sometimes people ask for the best way to use "A" to accomplish "B", then come back with how do I get "B" to do "C"? What they really want to know how to do "D" and if they would have said that at the beginning, someone might come back with starting at point "E" instead because then you can do "D" directly.