Can an Excel form be used to populate a sheet?

Patty Larson

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?

L_123

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

1. On submission you save each item in vba to a variable
2. You generate the correct query strings to update the column values based on the variables
3. you open the smartsheet form using the query strings to input all values into their respective locations in the form
4. you submit the smartsheet form in vba
5. 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.

Mike Wilday

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

https://help.smartsheet.com/articles/2478361-data-uploader

Sean Morgan

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

Thanks.

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

Patty

Mike Wilday

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

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

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?

https://help.smartsheet.com/articles/1850129-smartsheet-sync-add-on-for-google-forms

Patty Larson

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

Patty

L_123

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

1. On submission you save each item in vba to a variable
2. You generate the correct query strings to update the column values based on the variables
3. you open the smartsheet form using the query strings to input all values into their respective locations in the form
4. you submit the smartsheet form in vba
5. 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.

Mike Wilday

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

L_123

@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

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

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

L_123

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.