Community Groups are officially here!
We've released Groups, a new feature that enables us to connect community members of similar industries and interests in a shared, private space. You can check out all of the details here, including information about who can join, how to join, and what Groups are currently offered. Please leave your feedback through this Community Groups Feedback Survey.

Connecting with Google Sheets and Google Drive

mklubeck
mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭
edited April 22 in Qualtrics API

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

  • mklubeck
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭
    Accepted Answer

    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)
    }
    }

    1. Rename the Script to something recognizable
    2. 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.
    3. 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.
    4. You need to make these changes in two locations in the code above
    5. Save the Script
    6. Click Pubish, choose Deploy as web app...
    7. In the dialog box;
    8. Give the Version a title,
    9. Set it to execute as "Me" - your name,
    10. Allow "Anyone, even anonymous" to access the app,
    11. Click on Deploy
    12. Click on "Review Permissions"
    13. Choose your account
    14. Click on Allow
    15. Copy the "Current web app URL:" into your text editor
    16. We're going to put this value into Qualtrics (under Actions)
    17. Open your project in Qualtrics
    18. Go to Actions
    19. Add Action
    20. Name the Action
    21. Click on Event
    22. Choose "Survey Response"
    23. 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.
    24. Click on Add Task
    25. Choose Web Services
    26. Paste in the URL you copied last
    27. Under Request - Choose POST
    28. Under Content - Choose URL Encoded
    29. NOT using "Headers" - but are using Specify Data fields
    30. on the left side, put in the exact headers in your google sheet (capitalization matters)
    31. on the right side, grab via the drop down the information you want in your google sheet
    32. Click Save
    33. 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
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭
    Accepted Answer

    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

  • rondev
    rondev Community Member Wizard ✭✭✭✭✭

    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
    Vassilis LondonCommunity Member, Qualtrics Brand Admin Qubie ✭

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

  • Vassilis
    Vassilis LondonCommunity Member, Qualtrics Brand Admin Qubie ✭

    You are simply amazing! Thanks a million :D

  • Lhaslam
    Lhaslam SLC, UT, USACommunity Member, Qualtrics Brand Admin Qubie ✭
    edited April 2020

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

  • mklubeck
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭

    @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
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭

    @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
    Lindy2020 Community Member Qubie ✭

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

  • rondev
    rondev Community Member Wizard ✭✭✭✭✭

    @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
    belahm Community Member Qubie ✭

    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
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭
    edited June 2020

    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
    spurl Community Member Qubie ✭

    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
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭

    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
    kingre Winston-Salem, NCCommunity Member, Qualtrics Brand Admin Qubie ✭

    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
    JRoyal UC BerkeleyCommunity Member Qubie ✭

    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
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭

    @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
    JRoyal UC BerkeleyCommunity Member Qubie ✭

    @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
    mklubeck South Bend, IndianaCommunity Member, Qualtrics Brand Admin Sage ✭✭✭

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

  • bstrahin
    bstrahin Madison, WICommunity Member - Trial User Wizard ✭✭✭✭✭

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