Learn How To:

• Import quiz questions into Adobe Captivate
• Import quiz questions into Cornerstone OnDemand

Overview

This program utilizes Google Sheets and Google Apps Script to automate the process of copying/pasting quiz questions into both Adobe Captivate and Cornerstone OnDemand (CSoD). It has proven successful in decreasing a workload that would take hours to complete down to just a few minutes. There are two versions of this program. One version can be used for quizzes with 4 response options (i.e. A-D), and the other for quizzes with 5 response options (i.e. A-E). You will need a Google account to run this program.

Initial Setup

Template Sheet

Navigate to my task automation folder. Choose either the 4 or 5 response option quiz, right click the file, and select Add to My Drive. This will create a copy of the file in your google drive. I recommend setting this initial copy as your quiz template. Duplicate your template whenever you want create a new quiz.

Select the tab labeled dataMappingQuestions, and take a look at the following columns:

• Column B: Cell B4 and beyond serve three purposes.
  1. Categorizes the quiz. Navigate to the tab labeled questions and select a value from the dropdown menu in cell A1.
  2. The value selected in the previous step will also be applied to the name of the file that's created after running the program. Change the provided values to match naming conventions of your files.
  3. For people working with CSoD, columns A-C will need to be modified to match the test id's, names, and status associated with your CSoD account.

* Column E: Cell B4 and beyond serve one purpose. 1. These values help further categorize each question. Change the provided values to match your naming conventions. Navigate to the tab labeled questions and view any cell in the C4:C range to see the values in the dropdown menus.

In most cases, the questions tab will be the only section you'll be working with. So you can hide the remaining tabs if needed.

Navigate to the tab labeled questions. Fill out the spreadsheet with quiz questions and responses. Here are a few things to note:

A-D	A-E	Notes
Cell A1	Cell A1	Required: select a value from the drop-down. This value will be used for titles after running the program. As stated previously, these values can be changed by following this path dataMappaingQuestions tab > Column B.
Column B	Column B	Required: The question number of each problem.
Column C	Column C	Required for CSoD only: This value will be used for to further categorize each question after running the program. As stated previously, these values can be changed by following this path dataMappaingQuestions tab > Column E.
Column D	Column D	Optional: The file name of additional resources related to the problem.
Column E	Column E	Required: There are three question types that can be selected, True/False, Multiple Choice – Single Answer, and Multiple Choice – Multiple Answer. The selected question type will set the available answer options.
Column F	Column F	Required: Question to the problem.
Column G-J	Column G-K	Required: Character limit = 500. The number of response options required depend on the question type selected in Column E. For example if the question type is True/False, the response options in columns G & H are the only values required, and these values will be set by this app.
Column K	Column L	Required: As stated previously, the options available for an answer depend on the question type selected in column E.
Column L	Column M	Optional used for CSoD only: Character limit = 300. Explanation to an answer if learner responds to problem incorrectly. It can be used to provide additional information or point to a previous section to reinforce learning.
Column M	Column N	Optional used for Captivate only: Captivate quizzes can be grouped into question pools. This feature captivate separates questions into separate text files that can be imported into Captivate's different question pools.

Script Editor

Open either the 4 or 5 option file from your drive and navigate to the menu bar. Click tools and select Script editor. A new tab should open in the browser titled formatQuiz4Options_SharedGitHubScript or formatQuiz5Options_SharedGitHubScript. Either tab should have following listed files:

1. captivate.gs
2. characterLimits.gs
3. choiceTF.gs
4. cornerstoneAnswers.gs
5. cornerstoneQuestions.gs
6. googleDrive.gs
7. menuBar.gs
8. objectQuestions.gs
9. playground.gs
10. quizCaptivate.gs
11. quizCornerstone.gs
12. quizProtocol.gs
13. reusableFunctions.gs
14. templateKnowledgeCheck.gs
15. validationRules.gs

If you can't locate these files, select File > New > Script File > name the file, then copy/paste the corresponding scripts located in the root folders of this GitHub repository.

Run the Search

The first time you try to run this app, Google will inform you the app must be approved. Select this google clould help link to learn how to verify the app. You can bypass the previous process by following these steps:

1. Navigate to the menu bar, click Admin, and select Format Captivate, Format Cornerstone, or Format All.

2. A pop-up will appear letting you know authorization is required. Click the button labeled Continue.

3. Select the Google account you wish to use. Then select the link labeled Advanced.

4. Click the link at the bottom. It should be labeled with the name of the script being used to run this app. Unless you rename the script it should be labeled as dailyReport_SharedGitHubScript.

5. Type the word Continue in the text box and click NEXT.

6. Once verified, the app should run. But, if it doesn't, repeat step 1 and that should do the trick.

7. If this is your first time running the app, wait for the script to finish running, then open your google drive and search within the root directory for a folder titled quizAutomation. The app created this folder. Notice a subfolder has also been created with the same name that you selected in cell A1 of the questions tab. All files will be placed in the subfolder.

8. Treat the quizAutomation folder's name as a unique id, ensure no other folder in your google drive has the same name. You can move this folder to another location in your drive, any time you run the app, the program will fish out its location.

Saving and Uploading Files

Navigate to the corresponding subfolder and open either the Captivate or Cornerstone files you want to upload.

• Example files are available for you to practice uploading at the following location ./assets/images/dataExamples

• Captivate

  • Click File > Download As > Plain Text (.txt)
  • Open an Adobe Captivate project. Select Quiz > Question Pool Manager... > Import GIFT File > select the file > Open

• CSoD

  • You will have to run the following steps for both Questions and Answers tabs located in the CSoD excel file.
  • Click File > Download As > Comma-separated values (.csv, current sheet)
  • Cornerstone OnDemand has specific for uploading tests questions and answers into their servers. Ensure files match their upload templates before uploading.

Modify the script

The questions tab has 4 example question that can be used to see how the program operates. This information can be removed altogether. However, any other changes made to either spreadsheet will require thoughtful consideration, because you will most likely have to make supporting changes within the script. Here are a few tips before getting started:

• To rename the spreadsheet tab names:

  • Click the drop-down icon on the tab that will be renamed, then click Rename.

  • Navigate to the menu bar. Click tools and select Script editor. You will have to run across each script to locate the corresponding sheet names, and replace the strings with the names you create. Ideally I would have created an object that houses the sheet names in one location, but didn't consider this when first creating the app. If you consider doing this, the reusable functions.gs file is a good place to store the object.

• Renaming Headers in the spreadsheet:

  • Navigate to the menu bar. Click tools and select Script editor and locate the reusable functions.gs file. Look for the object that houses the headers you replaced, and supply the new names in the respective locations. You might also have to search each script file to ensure all instances of the headers have been replaced.