Can an Excel form be used to populate a sheet?
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?
Best Answer
-
I've managed this in VBA before. If you are interested I can help you put your information from excel to smartsheet.
The general concept behind it is
- On submission you save each item in vba to a variable
- You generate the correct query strings to update the column values based on the variables
- you open the smartsheet form using the query strings to input all values into their respective locations in the form
- you submit the smartsheet form in vba
- close the browser & reset the form
In my testing this is a fairly robust and simple solution, i'd be happy to share the code.
Answers
-
You might try using the data uploader tool. It seems like that tool would work really well for your use case.
-
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
-
Thanks.
Sounds like it would be worth a try, but unfortunately, we don't have license to the add-ons.
Patty
-
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?
-
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.
-
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?
-
Thank you! I am not familiar with Google Forms, but will certainly look into this.
Patty
-
I've managed this in VBA before. If you are interested I can help you put your information from excel to smartsheet.
The general concept behind it is
- On submission you save each item in vba to a variable
- You generate the correct query strings to update the column values based on the variables
- you open the smartsheet form using the query strings to input all values into their respective locations in the form
- you submit the smartsheet form in vba
- close the browser & reset the form
In my testing this is a fairly robust and simple solution, i'd be happy to share the code.
-
Hey, I'd be interested in seeing this in action. Have you done something similar in Google Sheets using action script?
-
@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
-
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.
-
@L@123 Thanks for sharing. Very interesting concept.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives