Can an Excel form be used to populate a sheet?

Options

The Smartsheet form logic does not support the levels of logic we currently have in an Excel form. Can the existing Excel form be imported into a sheet if the fields match the sheet columns?

Tags:

Best Answer

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    You might try using the data uploader tool. It seems like that tool would work really well for your use case.


  • Sean Morgan
    Options

    Hello @Patty Larson ,

    I feel this should work, as the Excel Forms, similar to Smartsheet Forms, are built upon Columns.

    You could create a blank test Sheet, and give this a try, but I feel this should work.

    Let me know how you get on!

    Regards

    Sean

  • Patty Larson
    Options

    Thanks.

    Sounds like it would be worth a try, but unfortunately, we don't have license to the add-ons.

    Patty

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    I would also try copying your data and pasting it into the sheet. A SHIFT+V paste should paste all your values into Smartsheet.

    I'm curious, what kind of formulation are you using that isn't available in Smartsheet?

  • Patty Larson
    Options

    I did not create the form so I'm not sure what formulas were used. It is a request form that pre-selects information based on an entry. The logic in forms can only show another field, rather than specific information from a dropdown in a field.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Have you considered using Google Forms and their logic to populate your smartsheet? This is a free add on that might help connect form entry to smartsheet and provide stronger logic for sheet data entry?


  • Patty Larson
    Options

    Thank you! I am not familiar with Google Forms, but will certainly look into this.

    Patty

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    Hey, I'd be interested in seeing this in action. Have you done something similar in Google Sheets using action script?

  • L_123
    L_123 ✭✭✭✭✭✭
    Options

    @Mike Wilday No, Google and my company have issues over googles confidentiality/tracking/security, and as such we do not use them or any of their services.


    Let me see if I can dig up my old excel file, link it to a sheet, and share the excel file and sheet with yall. I've shared part of this solution before, but I don't think i've given the whole thing out at once on the community

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 11/16/20
    Options
    Private Sub SSAutoFormSubmittor()
    
    
    'This is a basic VBA code to submit the values in A1:E1 in the currently active
    'excel sheet to a smartsheet form automatically
    '
    '
    'Set IE as object open explorer, and make it invisible
    
    Dim IE As Object
    
    Set IE = CreateObject("InternetExplorer.application")
    IE.Visible = False
    
    'Set variables equal to first rows values
    
    a = Range("a1").Value
    b = Range("b1").Value
    c = Range("c1").Value
    d = Range("d1").Value
    e = Range("e1").Value
    
    'set static smartsheet variables to column names {A, B, C, D, E}
    formurl = "https://app.smartsheet.com/b/form/xxxxxxxxxxxxxxxxxxxxxxxxx?"
    c1 = "A="
    c2 = "&B="
    c3 = "&C="
    c4 = "&D="
    c5 = "&E="
    
    fullurl = formurl & c1 & a & c2 & b & c3 & c & c4 & d & c5 & e
    
    'Navigate to webform, and wait until loaded
    
    IE.navigate fullurl
    Do
    DoEvents
    Loop Until IE.readyState = 4
    
    'Submit webform, wait until submission has registered then close explorer
        
    IE.Document.querySelector("button[type=submit]").Click
    
    Application.Wait DateAdd("s", 1, Now)
              
    IE.Quit
    Set IE = Nothing
    
    
    End Sub
    

    Just replace the xxxxxxxxxxxxxxxxxxxxxxxxxx with your url for your form, and change the A= &B= &C+ ... to your column names and this will submit the values in a1:e1 in your form.


    Please ignore the poor vba, I haven't used this for actually purposes, just for testing, and as such haven't optimized it past my initial typeout after it worked.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    @L@123 Thanks for sharing. Very interesting concept.

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 11/16/20
    Options

    Honestly its not the correct way to do this. The right way would be to use the API to submit the information in a more straightforward and faster way. But this bypasses any login issues, no api token use, and very little programming knowledge is required. So I guess you take some you lose some.

    If I were to put this into actual use I would definitely throw on some error checking, and probably use loops and data checking instead of a static range. But I think it's a decent POC.