Connecting with Google Sheets and Google Drive

mklubeck

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?

mklubeck

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.

mklubeck

If you'd like to see it with the images, try following this link https://nd.service-now.com/kb_view.do?sysparm_article=KB0018743

rondev

You can check this but other thing we can do is we can store uploaded file URL using pipe text of upload question in google sheet column, if this is helpful.

Vassilis

Could you please share how your friend synced Google Sheets and Google Drive with Qualtrics? I am looking to do something similar!

Vassilis

You are simply amazing! Thanks a million

Lhaslam

I've been looking for something like this. Thanks!

mklubeck

@Vassilis were you able to see the article by clicking on the link? Wondering if it's open to the public. If you have any questions (or improvements) don't hesitate to ask

mklubeck

@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.

Lindy2020

https://www.qualtrics.com/community/discussion/comment/21870#Comment_21870

@rondev Could you please explain how to store the uploaded file URL in a Google Sheets column? Thank you!

rondev

@Lindy2020 - I was referring to the same method that is mentioned in the above post or here. I have never implemented this, but am sure that at step 24 (from URL) you can pipe in the FileURL and send it to sheet.

belahm

Any help would be greatly appreciated!! We get a failed action status with the following output:

{
    "needRetry": false,
    "name": "TaskHandlerError",
    "message": "Exceeded max limit of redirects"
}

Any suggestions?

mklubeck

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.

spurl

https://www.qualtrics.com/community/discussion/comment/26021#Comment_26021

I'm also having this problem, and doing what @mklubeck suggested above didn't seem to help. Has anyone else been able to resolve this problem?

mklubeck

hi @spurl ,

Did you ensure that the settings are to "public" on the sheet? It can't be to your organization since Qualtrics isn't part of the organization. I have a user with the same error I plan to work with directly so I'll let you know what I find out.

kingre

I'm continuing to get the same error as above. I have done the suggestions, but no luck. Any solutions to this? Thanks in advance.

JRoyal

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!

mklubeck

@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).

JRoyal

@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!

mklubeck

@JRoyal Let me know if you find a solution for the simultaneous submissions! It would be very helpful.

bstrahin

@mklubeck this is a fabulous resource. Thank you for sharing with the community! I'm excited to implement this for some automated certificate generation.