Defaulting to A Date in a Smartsheet WebForm

Smartsheet‘s Forms allow a date to be selected when building the Form. Unfortunately, that [Today] button only selects today’s date, not setting the default to whatever date the form is used.

This has never really been a major problem for me. Once the form is submitted, I captured the row’s creation date, in effect capturing “today”. Based on the number of posts in the Smartsheet Community, however, I may be in the minority and that’s not always what is needed. What is needed is that the date DEFAULTS to the date it is viewed, but can be changed by the user of the Form. All of the other column types can have a default value, changeable by the end user.

I don’t know why Smartsheet’s dates don’t.

So here’s today’s task – get the [D1] column shown in this form to be dynamic and populated when the Form is open.

In the image above, the [Task] and [C2] columns are also filled in when the user opens the Form, but are not using the default value for the form builder. There’s lot of possibilities there that I won’t dive into in this post.

Method 1: Dynamic Bookmark

This first method owes much of the answer to Marc Knoll’s blog post you can read here. What he describes is creating a javascript function to populate the URL in a bookmark. I had to tweak a bit for Smartsheet, and the result is here:

javascript:function url() {

       var date = new Date();

       var y = date.getFullYear();

       var m = date.getMonth() +1;

       if(m < 10){m = ‘0’ + m;}

       var d = date.getDate(); if(d < 10){d = ‘0’ + d;}

       var date = y + “-” + m + “-” + d;

return ‘https://app.smartsheet.com/b/form/xxxxxxx‘ + “?D1=” + date +’/’; } window.open(url(),”_blank”);

with the things that need to change in red. Grab the URL for the Form and replace the example. Change the D1 to your date’s column name. No special characters needed. [End Date] would be

+ “?End Date=” +

This is NOT a URL, so unfortunately, you can’t just copy it into your browser’s address bar, but instead it is the URL/Location in a  saved Bookmark. I tried both Chrome and Firefox successfully.

I have a few bookmarks in my Chrome browser that open various Forms, so this fits right there if I need it.

The main drawback I see is that we can’t go to each user’s browser and set them up with a new bookmark. Well, maybe we can, but I don’t know how and don’t want to even if I did.

Method 2: Smartsheet Hyperlink

The second method uses a cell in a Smartsheet Sheet to build the URL directly.

This formula in a cell

=”https://app.smartsheet.com/b/form/xxxxxxxx” + “?D1=” + TODAY()

will default to Today’s Date just like the javascript bookmark will.

The user can just click the resulting hyperlink will look like this:

While I have a few Sheets that have the hyperlink available to the user of the Sheet (some on each row, some only in the first row or two of the Sheet), that won’t help users that can’t, don’t, or shouldn’t be in the Sheet. To resolve that, the solution I like best (caveat: I didn’t have a solution yesterday) is to create a Report that points to the Form URL and drop that Form onto a Dashboard, like so:

Dashboards can be shared to users that can’t see the Sheet or access the Report directly and because it is showing a Report (other Dashboard Widgets did not yield satisfactory results), it could show multiple Forms across the Smartsheet system. I can see a few new uses for that too.

Final Thoughts

For nearly all of the use cases swirling in my head, Method 2 will probably be the one I go with. If you don’t have access to Dashboards, Reports should still be in your toolbox, it just won’t be as accessible to users outside your team. By putting the URL builder into Smartsheet, it would be a simple matter to have the Form default to something else (for example, the upcoming Friday), but that is left to user. Also, if you need to add dynamic default values (is that an oxymoron?) to other columns in the form, then each additional column has the following pattern

+ “&C1=Default Value

or

+ “&C1=” + some Smartsheet formula/value

If you like this post, please “Like” it. Every time my post gets liked, a child gets an ice cream cone. Or I’m more likely to post another one. It is one of those two, I’m sure.

If you are new to Smartsheet and want to check it out, click here. If you have questions about this post, add them below. If you have questions about something else in Smartsheet, post on the Community. I occasionally stop by to answer them and so do a lot of other talented people.

 

 

 

Comments

Julie B

This is very helpful as i am building several forms now.. So thank you..
Not sure how to “like” your posts (where is that button?), so i am posting a comment instead.. thank you for keeping these pages up.. I requested access to one of your sheets, but i doubt its still active (thats ok too)..
If those links are dead (not active sheets anymore), please let me know and i wont submit any requests for access when reading your articles.

    Ronin Global

    Thanks for reading and the thanks.
    I will check on the sheets and if able, get you access shortly.
    Any and all of work-arounds/guides may be outdated.
    Cheers.

Write a Reply or Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.