Connecting with Google Sheets and Google Drive
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 Answers
-
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.- Create the Google Sheet.
- Name the Google Sheet
- Name the specific tab (sheet) for your data ("results" is often a good choice).
- Grab the Google Sheet URL
- In the browser window, you'll see the Sheet ID.
- Copy the code after ".../d/" and before "/edit..."
- Copy this code to a Google Doc or another text editor.
- Open the Script Editor in Google Sheets
- On the Google Seet, top line menu select Tools
- Under Tools, select <> Script Editor
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)
}
}- Rename the Script to something recognizable
- 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.
- 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.
- You need to make these changes in two locations in the code above
- Save the Script
- Click Pubish, choose Deploy as web app...
- In the dialog box;
- Give the Version a title,
- Set it to execute as "Me" - your name,
- Allow "Anyone, even anonymous" to access the app,
- Click on Deploy
- Click on "Review Permissions"
- Choose your account
- Click on Allow
- Copy the "Current web app URL:" into your text editor
- We're going to put this value into Qualtrics (under Actions)
- Open your project in Qualtrics
- Go to Actions
- Add Action
- Name the Action
- Click on Event
- Choose "Survey Response"
- 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.
- Click on Add Task
- Choose Web Services
- Paste in the URL you copied last
- Under Request - Choose POST
- Under Content - Choose URL Encoded
- NOT using "Headers" - but are using Specify Data fields
- on the left side, put in the exact headers in your google sheet (capitalization matters)
- on the right side, grab via the drop down the information you want in your google sheet
- Click Save
- 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.
2 -
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
1
Answers
-
Could you please share how your friend synced Google Sheets and Google Drive with Qualtrics? I am looking to do something similar!
0 -
You are simply amazing! Thanks a million
0 -
I've been looking for something like this. Thanks!
0 -
@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.
-1 -
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?
0 -
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.
0 -
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?
0 -
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.
0 -
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!
0 -
@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).
1 -
@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!
0
Categories
- 8.7K All Categories
- 210 Community Information
- 552 CX Dashboards
- 7K Developer Corner
- 307 Employee Experience
- 3 Expert Knowledge
- 349 Stats iQ
- 2.3K Survey Platform
- 5K Survey Platform (Before March, 2021)
- 6 UX & UX Research
- 18 Virtual XM Events
- 330 Website / App Feedback
- 190 xFlow
- 207 XM Directory
- XM Discover