Setting up a new Google Sheet for the Reporting Widget
Below follows the process to set up a new Google Sheet in your own Google Account and enable it to work with the Reporting Widget.
- Sign into your Google Account.
- Open this URL to access the spreadsheet template: Google Sheets Template URL
- Click “File” – “Make a Copy…“
- Enter the name that you would like the Sheet to have (typically this would be your course name or another identifier for your course) and click “OK“.
- Once the sheet has been copied and the script loaded open the CPGURU menu and select “Reset All“.
- Click “Continue” in the Authorization Required Prompt.
- If applicable select your Google Account (this step may not appear for everyone).
- Google will display a screen stating “This app isn’t verified” with a red warning triangle. Click the “Advanced” link at the bottom and scroll down to the bottom.
- Click the “Go to Script (unsafe)” link.
- Click the “Allow” button.
- Click “Tools” – “Script Editor” to open up the Script window.’
- When the Script window has opened go to “Publish” – “Deploy as a web app..“
- In Project Version leave this as is.
- In “Execute the app as:“, ensure that this is set to “Me (your google account“.
- In “Who has access to the app:” select “Anyone, even anonymous“.
- Click the “Deploy” button.
- Copy the “Current web app URL” and save it somewhere for later reference. This URL is needed when we insert the widget in Adobe Captivate.
- Click the “OK” button and close the Script window/tab.
You have now set up a Google Sheet that will be able to retrieve data for a particular course.
If you have more courses that you would like to be able to store data for in Google Sheets, you must repeat steps 2-16 in the above process for every course so you get a unique web app URL for each course.
You can watch the above process as a video here:
Note: The warning that Google shows about the App is not verified is not dangerous. It is purely because it is not possible for me to get the script verified with Google as you are copying the sheet and script to your own Google Account.