How do you set up Google Drive to store test results?

We've actually put a template on Google Drive that you can use to get started. Here are some steps to follow.

1. Access the Google Drive Templates > They've recently removed the "Create from Template option", so you'll need to go directly to the URL: https://drive.google.com/templates?

2. Search for Lectora.

3. You'll see a few options:

- Lectora Test/Survey Submission: Use this template if you plan to prompt for the user's name.

- Lectora Test/Survey Submission - Anonymous: Use this template if results are meant to be anonymous.

- Lectora Form Submission: Use this template to submit Lectora form results.

4. Select "Use this template". Google will then open a form with a number of text entry questions. By default there are 50. If you have more than 50 questions, you can add more. If you have less, you can delete them. You don't have to delete them - the resulting spreadsheet will just have more columns than you need.

If you wanted to build your own form, you would just need as many text entry questions as there are questions in your test.

5. The URL for the form is all you need to provide Lectora in the Settings dialog. You can find this on the Test Ribbon on the Results Tab. Select "Custom Results Submission", then Submit to Google Drive. Select Settings and paste the URL.

6. To see results, from the Form, select See Responses > Spreadsheet. The spreadsheet is automatically created when the form is added - the two are linked together. You can also find this on your list of documents in Google Drive - it will be named "Copy of ". You can always rename this.

7. Within the spreadsheet, you can rename and resize the columns as needed. If you need to edit the form - select "Form > Edit Form" from the Spreadsheet's menu.

Unlike other submit options, you can test this directly from Run mode. You should see your answers get posted into the spreadsheet as soon as you submit the test.

Finally, you will need to do this for each quiz you have.

When you select Use This Template, Google automatically creates a copy for you and saves the resulting spreadsheet (where the answers are tracked) in your list of Google Drive documents.

If you make any changes to the form, there is a Save button in the upper-right hand corner.

Make sure you've selected Google Drive from the Submit To dialog. Then verify you're using the correct URL in the Settings dialog. The form's URL should look like this:

https://docs.google.com/spreadsheet/gform?key=0Au3d6z3ilBiDdFYzX0hlUzJKbjFCV19uSi1rbkFoUmc&gridId=0#edit

As long as the test is graded, the score will be included.

I can't get this to work either. The spreadsheet doesn't populate any answers. Where would the score show up, since there is no column for that? Why does it list question numbers that I don't have on the edited form. Do we need to enter the variable name for each test question somewhere?

I got this to work partially for a test, but it's not doing anything for a survey I created. Do we have to name the file the same as the name of the test/survey? Is it necessary to fill in all the questions and answers in the template, or does it pull in whatever is in the variable anyway? Is there a tutorial somewhere?

Also, what goes in the Question Title field? Can I put the Question itself in there or does it need the object name?

I got this to work for a survey but there is no score appearing when I do this for a test.

For the Google form url to enter in Lectora, I copied and pasted the full URL from the spreadsheet view, then added #edit to the end. The actual "edit form" link from the spreadsheet did not work for me.

It doesn't appear that the question names and variables matter; it appears to pull the questions through in the order they appear in the title explorer within Lectora.

I also would love to find a way to send server info (user name, email, browser type, etc.) to the spreadsheet as I currently get all that info when generating a custom email with VBScript.

Edit: I made another one from the template and this time entered nothing for questions and answers. It seems to work fine. Still no test score though. One odd thing is that this does not work correctly from Preview mode. It will submit info, but not the test answers. It includes what some system info and yes, the test score (but not when published).

Alright, to get a test score, you need to leave an extra question on the form. I had been deleting all extra questions. Really, it seems to work best if I don't edit the form and just save it and edit it directly in the spreadsheet instead. I can change the questions names there and it still works. When I added questions and answers to the form edit screen, it seemed to place questions in the wrong order. There appears to be no reason to add those. It will pull in whatever answer is submitted. Again, I would love the ability to add more fields for other info.

The form questions (entry fields) are really just placeholders for Lectora to post the answers. If you wanted to pull in additional information outside of the test question variables, you could always populate hidden questions with any variable you like in Lectora, and just make sure it's inside the test. I do agree that the ability to include all variables is a valuable feature, though, and I've made sure to document this.

In the end, it doesn't necessarily matter what the form looks like, just that you have enough placeholders to handle all the variables you're trying to submit. Users will never see the form, and as long as the column headers in the spreadsheet are meaningful to you, that's what matters most.

We're planning on giving a demo of this functionality in an upcoming Inspiration Wednesday session, so be sure to keep an eye out!

@egwin 49388 wrote:

Thank you for your response. I'm on the right track now and I've gotten through steps 4 and 5 of your instructions. But I think I'm missing a step between 4 and 5. How do I save the form?

I tested my quiz in run mode and no results were sent to the template.

Also, how can I get the total score for the test to be submitted to the form? I'm not concerned with saving individual answers, I just want to save the final score.

I really think seeing the individual answers here can be quite beneficial for both surveys and tests. It can tell me how many people get each question wrong. If it looks like a large number of people are getting the same question wrong, it is a sign that we are not teaching that particular concept correctly (or that the question may be badly worded).

Does anyone know how to edit the spreadsheet to add other info besides the test answers? I can't find anything to edit the template itself. There must be some way to add a test score column.

Where are the templates? I can't find them at google. Do you have the date for the demo yet? THANKS!

You can find the templates here: https://drive.google.com/templates?q=Lectora&sort=hottest&view=public. You should see 3 available Lectora templates. Once you select "Use this template" a copy will be created in Google Drive.

There will be an Inspiration Wednesdays webinar on Questions and using Google Drive to store test results on April 10, 9:00 AM and 1:30 PM EDT. You can register here: http://lectora.com/free-e-learning-webinars

Ok, so I can see that in Inspire, and in the Test results, you can select a checkbox that will prompt for the Student's name, but isn't there a way to pull that information from the LMS/SCORM packaging? I know that you can pull that username/etc. into Lectora using AICC_Student_Name and other variables like that. Is there a way that we can populate the GoogleDoc with that information instead? It seems pretty straight-forward...

Joe

@Rhys 51213 wrote:

You could do this by adding an input box. Put an action at page level to set the content of that box to the AICC_Student_Name and another action to submit the result to the Google Drive.

Ok, I think I need a little hand-holding with this...

Currently, I have the standard Lectora Google Doc that is recording the timestamp, question answers, and final score. In the column (in GoogleDocs) next to the timestamp, I'd like it to record the username (via SCORM)... Renaming that column in google docs is a no-brainer, but simply renaming that column doesn't change the fact that the first question's results are recorded there.

Second, for the test results, submitting that to GD isn't an issue because that's built into the Test mechanism. I'm not seeing how I can send the contents of a text box to GD...

Wait... I think I have it.

Here's what I did:

I created a test. On the first page, I have two test questions. The first is a Fill in the Blank, and the second is a multiple choice. The text box on the fill in the blank has an "On Show Page, Change Contents" action on it that pulls in the username (or any other variable data) and drops it into the text box. BUT - That fill in the blank question is initially hidden. That way, the user just sees the one multiple choice question on the page, and moves on, not knowing that their username is passed on.

How'd you do it?

Thanks for tickling my brain!

Joe

Cool - yea, I just set that test question to a point value of 0. Thanks again!

You should be able to access the template forms here:

https://drive.google.com/templates?q=lectora

I was able to find this by doing a Google search for "Google Drive Templates" and then searching on that page for "lectora" - just in case they move it again!

Hi Dan,

When Google upgraded Google Forms with a new API, we had to make changes in Lectora to work with these updates. These changes were made in Lectora 12, so unfortunately, if you're using Lectora 11 you're still going to experience issues.

If you have any questions about moving to Lectora 12, let us know how we can help.

- Laura

Is there a way to send variables to a Google Document without creating a form? If you have to create a form, how can you have the Lectora course fill in the form using variables where users do not input the data.

For example, I need to track each time a course is accessed (outside an LMS), it doesn't matter how many times the same person accesses. The client does not want them to have to login nor wants them to fill out a form. They basically need to track usage of the content (grant requires this information).

Or, any ideas on how I can track course usage without getting anything from the user. I proposed to the admin that we use Google Analytics but they were concerned that it would not work because the URL will have a question mark. Is this true?

I think I am just too tired today to think this outside of the box.

I tried creating a Google Form, then copying the URL and placing on the next button, OnClick Submit Form, but it does not populate. I think I am missing a step. I am on V12.0.2 of Lectora Publisher.

There are multiple ways of getting data from your learners into a document, and it seems you are confusing those.

A. Silently submit a form to Google Form.

1. Create a form with any kind of data in Lectora.

2. In Form's Properties, select Submit to Google Drive. You can choose to submit only form values or add all other variables in the course or add a custom parameter (e.g. a password or course ID).

3. Click the green pencil and enter an address for your Google Forms (created from a template as detailed in the help file).

Your data will be submitted to a form. Student's won't have to fill out anything, they won't see the Google Form.

B. Add a (prominent) Google Form to your course.

1. Create a Google Form.

2. Add it on a Lectora page using Web Window.

3. Your students will have to fill it out and submit. You cannot submit it using a Lectora button and you cannot add any extra variables to the form.

Hope this helps.

@krixquet 64429 wrote:

Thanks Sergey, I'm trying, I did select send to Google Form but the form has not received the data, so something isn't working. Not sure what I am doing wrong but I will keep trying!

Sometimes when you preview from your local hard drive (inside Lectora or after publishing), Google will refuse communication with your course. Try uploading the course on LMS / web server and see if it helps.

Does anyone know how to add additional columns? I can add them but they don't pull the information in from Lectora.

That is a similar problem I am having, if I create my own form, no data transfers. If I use the template, and add columns, data doesn't transfer... so, I wonder if the suspense data maxes at 50 questions or if John Blackmon did something special to the Excel worksheets that make them work.

From earlier in this thread, it appears this should be possible but it doesn't seem to work for me:

@lauram 49366 wrote:

4. Select "Use this template". Google will then open a form with a number of text entry questions. By default there are 50. If you have more than 50 questions, you can add more. If you have less, you can delete them. You don't have to delete them - the resulting spreadsheet will just have more columns than you need.

If you wanted to build your own form, you would just need as many text entry questions as there are questions in your test.

Also, there appears to be a missing column between column Z and AA. I always have to add a "dud" hidden question to bump up the data that goes missing here.

Well, it turns out, it was working, just not with a form I built, but only from the Template by John Blackmon. I honestly have no idea why. Also, it doesn't work if I have it in ReviewLink but does if I preview it in a browser from Lectora. Now to test it somewhere else.

Thanks Sergey, I'm trying, I did select send to Google Form but the form has not received the data, so something isn't working. Not sure what I am doing wrong but I will keep trying!

So, the reason John Blackmon's template works, I noticed when I viewed the source code of the form, is because he has already manipulated the form to include content Lectora requires to make the two talk to each other. So, if you know how to edit Google Forms in code... you can. You will need to do this as well if you add questions beyond the 50.

Correct, it sounds like it should work, but I have never had it work since the first time they introduced this in Inspiration Wednesdays. I have always had to use their template and never have more than 50 questions. Well... for now, it is works good enough for me; however, I am going to research if I can actually tell it which column to place information.

For example, in HTML coding, you can use ID names for each tag. A person can input a web address and include the ID to go straight to that location on the page (www.mypage.com#bottom). I wonder, if with the URL for the Google Spreadsheet, can I add something at the end of the URL to tell it to send that data to a different sheet in the workbook or different column?

I ask, because in my scenario. I only need to capture how many times the learner launches a course (no matter the number of times) and on the last page, how many learners actually make it to the end of the course. We are not tracking how many make it to page 2 - 2nd to last page.

The only way to do this, that I could figure, was to put a form on page 1 and have it submit on show and a form on the last page and have it submit.

Thank you for your response. I'm on the right track now and I've gotten through steps 4 and 5 of your instructions. But I think I'm missing a step between 4 and 5. How do I save the form?

I tested my quiz in run mode and no results were sent to the template.

Also, how can I get the total score for the test to be submitted to the form? I'm not concerned with saving individual answers, I just want to save the final score.

Firstly thanks to everyone who has contributed to this topic, it has potentialy been really benificial to me.

I do have a slight problem though. I have set up the Google Drive, added my form to ETC and can post data to the Google Drive just fine when I test it in Run Mode. However when I publish my content, published as Web (HTML), I get an error message saying Not Found (see image below). I get no indication what is not found and there is nothing else to go on to help me resolve this. When I first published the course I got a different error message but this was resolved by checking the Use JavaScript Title Manager.

[ATTACH=CONFIG]393[/ATTACH]

Any help with this problem would be great as I would really like to use this method for recording data.

Did you ever figure this out? I am having the same issue. Runs great in run mode, but will not work in SCORM or HTML.

@Rhys 49511 wrote:

Firstly thanks to everyone who has contributed to this topic, it has potentialy been really benificial to me.

I do have a slight problem though. I have set up the Google Drive, added my form to ETC and can post data to the Google Drive just fine when I test it in Run Mode. However when I publish my content, published as Web (HTML), I get an error message saying Not Found (see image below). I get no indication what is not found and there is nothing else to go on to help me resolve this. When I first published the course I got a different error message but this was resolved by checking the Use JavaScript Title Manager.

[ATTACH=CONFIG]393[/ATTACH]

Any help with this problem would be great as I would really like to use this method for recording data.

Hi,

Yes I did find a solution. (forgive slightly vague instructions I cant log into my google drive at present) If you go to google drive and open the file. In the open file there is an option somewhere on screen to change the sharing options. when you select this you are given options to make the file avilable to public, only people with link, and one other I cant remember. There is also a link to the file, using this link seemed to resolve the issue for me.

Sorry I cant be more specific in the instructions, I hope you can work it out from that.

You could do this by adding an input box. Put an action at page level to set the content of that box to the AICC_Student_Name and another action to submit the result to the Google Drive.

almost exactly the same only I use an entry field rather than a question. You also need to check the Include All Variable Values check box on the results tab. Reason I do it with an entry field rather than a test question is so not to impact the overall test scores.

Since I set this up last year, it seems the location for the form no longer exists. Do you know to where it has been relocated?

Thank you.

@lauram, I appreciate you posting steps on getting Lectora wired up with Google Drive. I went through the steps but am receiving some errors. Here is what I have done so far:

I downloaded the Lectora Form Submission template from: https://drive.google.com/templates?q=Lectora&sort=hottest&view=public

Within Lectora Publisher v. 11, I went to drop in the Google Drive URL for the spreadsheet version of this Lectora Form Submission template. Here are my steps that I went through:

Behavior & Results > checked option for Custom Results Submission

Submit To: Google Drive

Settings > Submit to Google Drive URL: https://docs.google.com/spreadsheets/d/1w5wgVkciIjIqyu4zk6J8CzfMesXIvoxZkm3zplp330U/edit#gid=0

Upon copying the Google Drive URL from the web address bar and directly pasting it into the Lectora settings, I received a pop up error saying that the URL was not properly formatted and that I should get the Google Drive URL directly from the web address bar. It went onto say that the URL format should be something like:

https://docs.google.com/spreadsheets/ccc?key=1w5wgVkciIjIqyu4zk6J8CzfMesXIvoxZkm3zplp330U

Note the difference between the two URL formats: My initial URL was: .../spreadsheets/d/... while Lectora is requesting: .../spreadsheets/ccc?key=....

I went ahead and formatted the Google Drive URL as requested by Lectora, however it prompted me with another error saying that the connection to the Google Drive account failed. This error occurred while testing in Run Mode.

Is there anything that I may be doing wrong, or would this be an issue with version 11 of Lectora Publisher?

Any insight would be great, thanks!

- Dan

As a new user, I'm trying to set up a title so test results are stored in Google drive. I'm using the template mentioned but no results are being posted. I can't seem to find a tutorial that covers the basics on this. Can anyone point me in the right direction? thanks

We just wrote an article up on this topic in the Knowledge Base. You can access it here:

http://community.trivantis.com/knowledge-base/using-google-drive-to-collect-results-from-lectora/

Thank you for the article in the knowledge base. It's incredibly helpful and easy to follow. It didn't take anything to get my course test to submit data to Drive.

Now, I am wondering if there is a way to customize the data coming in.

Currently, I am working on a 40 question randomized test. The 40 questions are pulled from a batch of 98 questions. The spreadsheet automatically collects data for Timestamp, Name, the answer selected to each question, and Score. This being a randomized test, it makes the spreadsheet difficult to read without any indicator to which question each answer is referring to.

I also see an issue with the number of questions. If that number varies, the score does not have its own column, but just designated to the last column in line. If the data columns exceed 50, the score does not appear at all.

Is there a way to designate specific columns to only populate specific information (ie. name, score, question_0001)? Or is there any documentation already to help me customize the spreadsheet?

When I use the link https://drive.google.com/templates? to search for the Lectora template, I can't find any template. I can only see my own documents.. does anyone know where I can find the right template to store my survey results in a Google Drive spreadsheet?

Thanks!