Using Google Drive to Collect Results from Lectora

Lectora Inspire, Lectora Publisher, and Lectora Online include the option to submit your Test, Quiz, Survey, or Form Results to a Google Form that will create a Google Drive spreadsheet with all of the results listed. You can use a spreadsheet that you control on Google Drive to collect Lectora data instead of or in addition to using an LMS or custom server script.

This process will be updated in an upcoming release (currently planned for Fall 2018) of Lectora Inspire/Lectora Publisher/Lectora Online.

Before continuing this article please take note of these important pieces of information:
  • The time results can take to show in the Google document can vary. It can range from almost instantaneous to a few hours, but if you follow the directions below, the results will submit correctly.
  • Submitting your Lectora results to a Google Drive spreadsheet is currently only supported in Lectora v12 or greater and Lectora Online v2.0 or greater. Google has made changes to the process and it will no longer work in older versions of Lectora and Lectora Online.
  • The learner completing the test needs to be using the latest version of Internet Explorer, Chrome, Firefox, or Safari. Google no longer supports Internet Explorer 8 & 9 when submitting results to a Google Drive spreadsheet.
  • Each template includes 50 question placeholders that can be used to collect data from your learner.  It’s not recommended that you add additional questions or form elements into your course if you plan on using Google Drive to collect your data.
  • Unfortunately, Google changed their forms recently such that we can’t change or edit the templates (or even create new ones!), without breaking the form submission from Lectora. You cannot use a spreadsheet or template that you’ve created. But you can customize one of the approved templates.
  • Each time you open one of these forms, Google will prompt you to upgrade to the new version of Google Forms. Always click “Not now”. Do not upgrade the form. Only the older version of Google Forms is compatible with Lectora. The new upgrade to Google Forms will not work.
  • If your course is published to an LMS and you want the learner’s name to be submitted to the Google Drive document, even if you have the “Prompt for Student’s Name” checkbox selected in the Test Results ribbon, the course will automatically gather that data from the LMS and submit it to Google Drive upon test submission, without ever prompting the learner for their name.
  • The submission works by sending a URL back to Google with all of the test data embedded in this URL. That being said, there is a 2048 character size limit for this URL. Please keep that in mind when developing your test. If you have a lengthy test with longer, or essay length answers, you may not be able to submit your results to Google Drive properly. We always advise that you test as you go, add a few questions, then preview and test to see if the results were submitted.
  • Remeber that your browser has a 4KB data input limit for suspend data (cookies) that can be held during each instance. If the collective data stored for answers is more then the limit you may run into issues.
  • You can test submitting your results to Google Drive with the Preview Mode in Lectora v12 or higher and Lectora Online v2.0 or higher, as well as in published content. But, due to a change in Google’s API, submission to Google Drive no longer works in Lectora v11.
  • Issues of the form skipping the 16th or 25th field (sometimes fields in between these two fields). One alternative is that you can place a dummy question so that the system continues the numbering convention of data is not being transferred.  This is accomplishable using an invisible question in the place of that 16th and 25th field so that all of the answers are in place. We are unable to make changes or fixes to our Google Form templates because of the changes Google has made, so this workaround is currently the only way to correct it.

We’re going to use Lectora Inspire and a Test as an example but the same steps would apply to Lectora Publisher or Lectora Online and a Survey or Form could be used in place of the Test.

Step 1 – Lectora Setup

  1. Select your Test Chapter in the Lectora Title Explorer to access it’s contextual properties.
  2. Go to the Results ribbon.
  3. In the Results Submission section of the ribbon, select the checkbox next to Custom Results Submission and change the Submit To drop-down box to Google Drive.
    Test Results - Google Drive
    Note: You can also choose to prompt the learner for their name or to show a message to the learner after the results have been submitted if desired. You can see these options checked on the right in the above screenshot.

Step 2 – Choosing a Lectora Template in Google Drive

The next thing that you will want to do is download one of the provided templates. Three templates are provided to meet your needs. Choose from the following:

Clicking one of the template links will prompt you to make a copy of the template. Click the “Make a copy” button. This file is now on your Google account, attached to your Google Drive.
Google Drive - Copy Document

For this example, we will be using the Lectora Test/Survey Submission template.

Note: Do not upgrade the form. When it opens it will prompt you to upgrade to the new version of Google Forms. Click “Not now”. Only the older version of Google Forms is compatible with Lectora. The new upgrade to Google Forms will not work.
Google Drive - Upgrade Forms

Step 3 – Adding the Form URL to Lectora

The next step that we will want to do is grab the URL for the form. The URL is what will tell the course to send the test results to your specific form on your Google account. To do this:

  1. Select the Eye icon or View Live Form from the menu.
  2. A new window or tab will open in your web browser. The URL that you want to copy is now in the Address Bar/URL Bar of your browser when you are viewing the live form. Copy that URL.
    Google Drive - URL
  3. Next, go back to your Lectora title. If it is not already selected, please select your Test Chapter from the Title Explorer again and go to the Results ribbon at the top. In the Results Submission section of the ribbon, make sure that you still have Custom Results Submission checked and from the Submit To field, Google Drive selected. Click the Settings button just under that:
    Lectora Custom Results Settings
  4. A Submit to Google Drive popup window will appear where you need to paste in your copied URL into. Copy the URL into that window and click OK.
    Lectora Submit to Google Drive
  5. Your Lectora title is now set up to submit results to your spreadsheet on Google Drive
  6. You can click the View responses button on the Google Form to view a spreadsheet containing the results from your title.

For more information about the settings and options within Google Forms,  please reference Google’s help documentation: https://gsuite.google.com/learning-center/products/forms/get-started/#!/. Not all options may be supported when used in conjuncture with Lectora so you’ll always want to test the functionality before publishing and sharing with your audience.

Additional information can also be found in the Help for Lectora (LINK) and Lectora Online (LINK)

Preview image

Tags: , , , , , , , , , , ,

Comments

  1. Profile photo of ClaudiaP
    ClaudiaP

    Hello! thank you for posting this helpful document!!
    I’m wondering if someone has any idea what might be happening with my test results.
    The title I created has a test chapter with 20 questions. Questions 1-10 is a pre-test assessment. Questions 11-20 are spread out throughout the title’s different chapters.
    When submitting my results the google docs spreadsheet only tabulates results for questions 1-10, question 11 has records a random number and no data for questions 12-20.

    Any idea what I might be doing wrong?

    Thank you!

    Claudia

  2. Profile photo of Rhys W
    Rhys W

    I have been using Google Docs to store my Lectora test results for some time now. Today when I have tried to set up a new one I get the error message in Lectora telling me that the link is not in the correct format. I have followed the same steps as always and even gone through the above tutorial in case something has changed.

    Any ideas why my links are not correct? They are copied and pasted using the steps in this guide

  3. Profile photo of Evan Harris
    Evan Harris

    Rhys, from our interactions on your support case, you were using V11 to develop your course in. I know that we are now going to make a change to place this information near the top of the article, however Google Drive submission no longer works in V11 due to a change made by Google. V12 has the fix to compensate for this change by Google already implemented, so you can upgrade to V12 in order to have this process function correctly.

    As for your specific error message that you were seeing, V12 does have a slightly modified process for submitting to Google Drive in that the URL that you copy+paste into Lectora is different. In V11 you would just copy the URL of the main spreadsheet view of the form while in V12 you need to use the ‘Go to life form’ option as exemplified in Step 3 in the article above.

  4. Profile photo of Ivan Mesa
    Ivan Mesa

    I used it before and it worked fine, I have some old tutorials that continue reporting to Google drive without issues.
    Yesterday, I created a new tutorial using the latest Lectora Inspire version.

    I have a test with 24 questions and each of the templates is giving me a different problem:
    1. If I use Lectora Test/Survey Submission – Anonymous, it does not report my question number 16. It reports all the other questions and the final score at the last column.

    2. If I use the Lectora Test/Survey Submission, it reports all the questions, but it does not report the final score

    Any ideas of how to solve this mystery?

    Thank you in advance,
    Ivan

    1. Profile photo of crandallm
      crandallm

      I am having the same problem. Lectora Test/Survey Submission– Anonymous skips entry 17 and Lectora Test/Survey Submission skips entry 25.

      1. Profile photo of Brian Roemer
        Brian Roemer

        Same here…question 16, no matter which question falls into the 16th slot, won’t write to the “anonymous” sheet. Has anyone solved this issue? I’m considering placing a dummy question (not visible) as a placeholder for a temporary fix. Thanks!

    2. Profile photo of Mike Nelson
      Mike Nelson

      I am having the same problem. One of the answers gets omitted from the Google spreadsheet.

      1. Profile photo of Trivantis Support
        Trivantis Support Post author

        More information on the skipped items bug is available here: https://community.trivantis.com/forums/topic/is-there-a-new-google-doc-template-that-accepts-all-data-sent-to-it/

        Our CTO John Blackmon explains that Google recently made some changes to their Docs and we are unable to change or edit the templates, without breaking the Docs submit completely.

        But, he adds, “Since the issues are at least consistent, they can be worked around by placing a dummy, invisible question in the place of that 16th and 25th slot, so that all of the answers are in place.”

  5. Profile photo of Andy Lockwood
    Andy Lockwood

    Results are collecting just fine following these instructions, thank you.

    I am having a problem with the fact that it only submits answers not the questions. Is there anyway (variables?) to force Lectora to include questions with their corresponding answers?

    Ideally, I’d just want the automatic test results at the end of the test to be shipped off to a database or spreadsheet for recordkeeping.

  6. Profile photo of Zachary Siejka
    Zachary Siejka

    This is awesome. I added a step 2.5
    Once you choose the template and it shows up in your Drive
    1. Open it and select File>Make A Copy,
    2. Open the copy & rename it (i made it match the name of my test in Lectora).
    3. Select Responses>Change Response Destination
    4. Select [New Spreadsheet], Check the name assigned…it should match your renamed form and end with “(Responses)”
    5. Click [Create]

    I actually did this twice because I needed two different URLs for two separate quizzes inside of a course. Users select a job title at the beginning and then take the appropriate exam. The results are coming over fine from Lectora Preview Mode and the version I put on the LMS. So far it is just 7 and 5 questions to test this functionality. I should have the full 20 questions by the weekend and I’ll report back if I run into and responses not showing up on the spreadsheet.

  7. Mike Forder

    Since Google Drive no longer has a public template gallery, could someone share the template so that I could see if it still works?

  8. Profile photo of Christine O'Malley
    Christine O'Malley

    So, I’m trying to test this option for a client who has no LMS…

    I followed Zachary’s suggestion to rename the file, and create the results form. I also edited the first field in the (Lectora Test/Survey Submission) form to label it ‘clock number’ and I duplicated that field, to create a second ‘work location’ field. After that, I have 20 questions. I used the ‘Help Text’ of each field to insert the text question, thinking this would make it easier to match up Qs with As. I did not change any of the field types, thinking Lectora will ‘drop’ the answer from the course exam into the spreadsheet fields (which by the way are showing up just fine…. last but not least – I also set up the coding in Lectora for Google Drive.

    When I go through the exam Preview Mode nothing is changing in the Results spreadsheet. It’s been about 2.5 hours since I submitted. How can I figure out if I’ve done something incorrectly?

    Thanks for any tips you can share.

  9. Profile photo of SuzanneB
    SuzanneB

    Lectora Online – Forms to Google Drive

    Is there any actual documentation – besides the very limited information that exists in the Lectora Online Help, the https://community.trivantis.com/knowledge-base/using-google-drive-with-lectora/#comments page, and the one 2015 post in the forum – that provides more specific information related to using Lectora FORMS and submitting them to Google Drive?

    I have questions about all the forms, but will limit these questions specifically to JUST one of the items linked from Lectora Online Help, the LECTORA FORM SUBMISSION to GOOGLE DOCS, Lectora sample FORM at https://docs.google.com/forms/d/1diNtFqpoOdvPPXMIVvaWHCLOQj9DcQTX0mTwfF58tWY/copy

    1. Is there anything that specifies that a maximum number of fields can be transmitted using this form? The “Lectora Form Submission [Form] that Lectora provides to copy from contains 25 fields, but I cannot get more than 19 fields to transmit, as detailed below.

    2. Is there any Lectora Documentation that states how to input a VARIABLE in the ADDITIONAL VALUES popup window (right column) to have Lectora send the VALUE of the variable? Neither inputting just the variable name nor using the VAR(variableName) format works – in both cases Lectora only transmits the literal. I have been unable to find anything in Lectora Online Help about the Additional Values window.

    3. Besides the Online Help, is there some other Lectora documentation/manual that I am missing? I have the most current version of Lectora Online, having just purchased in May 2018. We are trying to publish our first Lectora course and cannot get past this posttest reporting issue.

    4. Regarding the actual transmission of data, it would SEEM that the either the Lectora Form submission code or the code behind the Lectora Goole Form has been altered since that 2015 information was posted. After HUGE amounts of testing and retesting, I have succeeded in getting data from Lectora to Google Sheets BUT the process is very much jerry-rigged and, due to apparent maximum number of fields that can be transmitted, I have had to combine some data/variable values into one field in order to obtain all the values I need. As such, I am very hesitant to use this in our published courses and am asking again for assistance to see if there is something I am missing.

    I can provide a small .pkg file if someone is willing to look at this. In my Lectora course, I have one Form: Form_12144.

    -By combining some data into one field, instead of each data item transmitting as a separate field, I have pared the tranmission down to 14 fields that are transmitted containing “real” data.

    -However, in order to do that, I also have had to include 6 DUMMY fields (i.e., fields that contain DUMMY data, but that don’t/won’t actually come through to the Google Sheet) plus 1 additional dummy field to get the first items in the ADDITIONAL VALUES list to transmit.

    -The first 3 entry fields consistently come through correctly. Entry_12184, Entry_12186, Entry_12188

    -To make the 4th field come through, I ended up having to input TWO dummy fields. In this case only (as opposed to the other dummy fields noted below), I also had to include two “fake” section names on the Google Form. NO data is successfully transmitted for these two fields, but unless both exist in the Form within the Lectora course as well as in the Lectora Form Submission document, none of the remaining fields transmit correctly.

    – Once those 2 dummy items are in place, the 4th, 5th, and 6th “real” data items transmit correctly. Entry_12194, Entry_12196, Entry_12322

    – It was then necessary to include 1 dummy field in the Form in the Lectora Course, but NO additional field in the Lectora Form Submission document.

    – After that, the pattern seems to be 2 more real data items, then 1 dummy item, at least as far as I am able to test, because the transmission always cuts off at 19 items.

    – So, after the 6th “real” data item, the Form within my Lectora Course has 2 more real data items (7th and 8th – Entry_12335 & Entry_12343), then 1 more dummy field in the Lectora Course (but NO field in the Lectora Form Submission document), then 2 real data items (9th & 10th – Entry_12347 & Entry_12351) and 1 dummy field, then 2 real data items (11th & 12th – Entry_12355 & Entry_12359) and 1 dummy field, then two final data items (13th & 14th – Entry_12333 & Entry_12408) with a dummy field.

    -That now makes 14 real (needed) data items plus 6 dummy items, only 2 of which actually impact the Google Sheet, even though no data actually reports in those 2 columns on the Google Sheet, so 16 “occupied” Google Sheet columns so far.

    – AFTER that point, 2 of the first three items listed in the Additional Values transmit – as literals, not values – then an item is skipped, then the next item from the Additional Values list transmits. This appears to follow the same pattern established after the first few columns of 2 data items transmitted successfully, then 1 skipped.

    – In the resulting Google Sheet, the first column apparently is automatically populated by Google Sheet code (date/time); the next 19 columns are populated as noted above. At that point, no matter how many Entry fields are included in the form in the Lectora course, the transmission cuts off.

    – It has taken several weeks of testing various combinations to get to the point where I can consistently get these results, even creating new Lectora courses and starting with a fresh copies of the Lectora Form Submission document and new Google Sheets. While at this time I can consistenly create and recreate this, I would prefer either to be shown where I am making an error or, if the code behind the Lectora Form Submission doc has an error, for that to be fixed.

    Since most of our customers do NOT have an LMS, it is critical that I create a reliable method of transmitting the posttest results.

    On the Trivantis Community page (https://community.trivantis.com/knowledge-base/using-google-drive-with-lectora/#comments) in a 2015 post about the Lectora-Google submission, it was noted that the 16th or 25th position did not transmit. However, there’s a big change from one field at the 15th or 25th position being “worked around” compared to having to do this after every 2nd or 3rd entry. Also, because this seems to have changed since the 2015 Lectora post, I don’t think I can consider, just because my current workaround consistently has worked for me the last few days, that this is reliable enough to include in a course to provide to our customers. And, this information was from a 2015 post. Now that it is 2018 and Lectora still is publishing Lectora-to-Google as a feature of Lectora, I am hoping that Trivantis/Lectora has been able to update the submission process.

    Help?

    1. Profile photo of Jennifer Valley
      Jennifer Valley

      The article has been updated with the most recent information and instructions. It would appear that it was updated in 2017 as well but the date of the original post was never changed. If you have any outstanding issues please let me know. I was told that you also opened a support case regarding the problems you encountered.

Your email address will not be published. Required fields are marked *