So, I had a coworker help me set up a Google Sheet App Script (really easy) per the information I found and have responses going into the Google Sheet (using Actions). One thing I can't find how to do is pulll in the Recorded Date (or End Date or Start Date) - but I have just been using "today's date" from the response...as that is essentially the same.
But NOW, I want to take a file upload (response) and save the file uploaded to Google Drive. Any ideas? has anyone done such a thing? Can it be done?
Best answer by
mklubeck
5 February 2020, 18:28
View original
Answer
We'll use a Google Script (this feature is part of the suite) and Qualtrics Actions. See attached video for a walkthrough
My internal knowledge base article has pictures that go with it...that unfortunately did not come through here.
1. Create the Google Sheet.
2. Name the Google Sheet
3. Name the specific tab (sheet) for your data ("results" is often a good choice).
4. Grab the Google Sheet URL
5. In the browser window, you'll see the Sheet ID.
6. Copy the code after ".../d/" and before "/edit..."
7. Copy this code to a Google Doc or another text editor.
8. Open the Script Editor in Google Sheets
9. On the Google Seet, top line menu select Tools
10. Under Tools, select <> Script Editor
11. Copy and Paste the following text into the editor window:
function doPost(e){
try {
var ss = SpreadsheetApp.openById("YOUR GOOGLE SHEET URL HERE");
var sheet = ss.getSheetByName("results");
Logger.log(e);
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var holderArray = [];
for (var x = 0; x < headers.length; x++) {
var tempValue = !e.parameter[headers[x]] ? ' ' : e.parameter[headers[x]];
holderArray.push(tempValue);
sheet.appendRow(holderArray);
var results = {
"data": e.parameter
, "holder": holderArray
var jsonData = JSON.stringify(results)
return ContentService.createTextOutput(jsonData).setMimeType(ContentService.MimeType.JSON)
catch (e) {
var error = {
"error": e
var jsonError = JSON.stringify(error)
return ContentService.createTextOutput(jsonError).setMimeType(ContentService.MimeType.JSON)
function doGet(e){
try {
var ss = SpreadsheetApp.openById("YOUR GOOGLE SHEET URL HERE");
var sheet = ss.getSheetByName("results");
Logger.log(e);
var data = sheet.getRange(2, 1, sheet.getLastRow() - 1,
sheet.getLastColumn()).getValues();
var jsonData = JSON.stringify(data)
return ContentService.createTextOutput(jsonData).setMimeType(ContentService.MimeType.JSON)
catch (e) {
var error = {
"error": e
var jsonError = JSON.stringify(error)
return ContentService.createTextOutput(jsonError).setMimeType(ContentService.MimeType.JSON)
12. Rename the Script to something recognizable
13. Change the text in the above script "YOUR GOOGLE SHEET URL HERE" to be your Sheet ID (you copied it into a text editor). Leave the quotation marks in.
14. Ensure you CHANGE Line 4, "results" to whatever name you gave to the tab (sheet). If you used "results" then you won't have to change the code.
15. You need to make these changes in two locations in the code above
16. Save the Script
17. Click Pubish, choose Deploy as web app...
18. In the dialog box;
19. Give the Version a title,
20. Set it to execute as "Me" - your name,
21. Allow "Anyone, even anonymous" to access the app,
22. Click on Deploy
23. Click on "Review Permissions"
24. Choose your account
25. Click on Allow
26. Copy the "Current web app URL:" into your text editor
27. We're going to put this value into Qualtrics (under Actions)
28. Open your project in Qualtrics
29. Go to Actions
30. Add Action
31. Name the Action
32. Click on Event
33. Choose "Survey Response"
34. You CAN add a condition so that the push to Google Sheet only happens if a condition is met. Or you can move all responses.
35. Click on Add Task
36. Choose Web Services
37. Paste in the URL you copied last
38. Under Request - Choose POST
39. Under Content - Choose URL Encoded
40. NOT using "Headers" - but are using Specify Data fields
41. on the left side, put in the exact headers in your google sheet (capitalization matters)
42. on the right side, grab via the drop down the information you want in your google sheet
43. Click Save
44. Publish your survey
You are now ready to test your form! Preview or Distribute the survey and try it out.
Note: the order of the headers (fields) does not have to match the order passed in...whatever order you put them in, in the google sheet will be how it’s saved.
Not sure how I would use the Specify Headers portion (perhaps to define the headers in google sheets? Not sure - if you find out, let me know).
NOTE: my users always want the Date/Time that the form was completed - and I can't seem to pull in any via the embedded data (piped text) - RecordedDate, EndDate, StartDate, none of these seem to work. So we end up using Current Date and Time (since they will essentially be the same thing). BUT, if anyone knows how to get the RecordedDate I'd love to know.
@Vassilis
- sorry, that message was waiting since Feb to send! I guess I never posted it! I take it it's doing what you want.
@Lhaslam
I'm glad I could help! It's nice for all those that want the Google Forms capability and the robustness of Qualtrics.
Not sure if this is causing your issue
belahm
but I did find an improvement (revision) to the process. DO NOT open the script editor in the Google Sheet.
Steps 8, 9, and 10 above. INSTEAD do this:
Eight: NOW go back to DRIVE, (recommend in the same folder as your Google Sheet), and use NEW, --> more... --> Google Apps Script. If you don't see this option, you may need to "enable" Google App Scripts so it's an option for you to create.
Nine: Select Google Apps Script
Ten: Copy and Paste the text into the editor window
All of the other instructions stay the same (from 11 on)... This is BETTER because it removes the needs to do some serious sharing / permission changes to the Google Sheet. BTW, it will work even if you move the app script to a different folder (or even user's drive) as it links to the URL that doesn't change.
I followed the directions above and it works splendidly. However, it appears that every instance of the invocation generates a 'failed' error in the Actions / Reporting tab (even though it
does
successfully append the record to the Google Sheet). When I view the error, it says:
Task Output
{
"name": "ACTIONS.ERROR_CODES.USER_CONFIGURATION_ERROR",
"message": "Exceeded max limit of redirects",
"needRetry": false
}
Seems a little different than the error mentioned above.
Thx!
JRoyal
not surprised...it seems to be a Google error that we can ignore. BTW, I have found that IF you have a LOT of these going out and you have simultaneous responses the Google Sheet may not get all of them. So I include a disclaimer that the data captured in the Google Sheet is accurate but "may not be complete." For a complete listing of responses, use a report. You can build one that mirrors what you are getting in Google Sheets, it can be "live" (like Sheets) by making it a public report and sharing the link. This is fully reliable (it won't matter if there are simultaneous submissions).
mklubeck
Thanks for clarifying - Will keep an eye out when next survey launches and responses are coming in fast and furious. Sadly, I find Qualtrics reports generally lacking. I have a live Google Data Studio dashboard linked up to my live GSheet (fed by Qualtrics) which meets my clients' needs perfectly. I wonder if something could be added to the script to handle the simultaneous access problem. Perhaps I'll look into that if I come across some time
Cheers!
I did always get the same error message until a week ago - now I have:
"name": "ACTIONS.ERROR_CODES.USER_CONFIGURATION_ERROR",
"message": "Exceeded max limit of redirects",
"needRetry": null
and it seems as needRerty is now null, qualtrics keep calling the script... so the data is now published 4 times...
Scanning file for viruses.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
This file cannot be downloaded
Sorry, our virus scanner detected that this file isn't safe to download.
