Update a sheet with a form

Itasca User

Hello Everyone,

Hope y'all are staying safe and healthy.

Currently building sales pipeline functionality in SS. Got my input sheet and some summary reports along with a dashboard. I'm now working on how to update the input/intake sheet. What I'd like to do is keep that sheet away from the reps - mainly so it doesn't get messed with (even though I know that I can lock rows/columns).

Form instructions say it only can add to a sheet. Is there a way for it to "update" data that is already in the sheet? Somehow use an "identifier" for a current entry, and using the information entered into the form, change the data that's in the sheet.

Thanks,

Jason Wirl

Andrée Starå

Hi Jason,

Yes, it’s possible as long as there is a unique identifier that we could use.

Have you thought about/explored using an Update Request workflow instead?

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Itasca User

Andrée,

Thanks for your note.

I did more research on Update Requests. This still seems to direct someone to the sheet itself. This could work (but would be "coolest" if it could work based upon exception. In other words, not global to all the opportunities in a sheet on a certain day of the week, let's say in a recurring Request, but rather only request the update if a row hasn't been updated for X days or weeks. But, as I understand the UR's, they can't be used with a form. Correct me if not ;-).

That said, how can a unique ID be used in a form to update field(s)? Within that, it would need some sort of logic to say that if a unique identifier is NOT entered, then create a new entry. How do most people do this for "updating" tasks in a sheet?

Jason

Jodie Kelley

Hi Jason,

Because I just was completing the e-Academy and just watched the Automated Update Requests, you can indeed restrict users to view only the cells they need to update.

You will need to Create a workflow. Under Automation, choose Manage Workflows.

You will want to first view the Automation Permissions. Select the level that the reps will need to the sheet in order to receive notifications and requests from the sheet. You can find this by clicking on the gear icon and Select Automation Permissions.

Select your Trigger : When a Date is Reached. Click on Run once and select Custom.

Select your recurrence and the time at which you wish the notification to be sent. (I prefer beginning of the day)

Under Alert someone, Select Who you'd like to have the alerts sent to for the column that needs to be updated.

Click on the Kabob (three dot) menu and choose Advanced Options. Here is where you can restrict their visibility to the rest of the sheet.

If you need this to be for one person at a time to only view their cells, you can clone this workflow for each additional person and select them from the Assigned To or Contacts column you've created.

I'm not sure if that will help you, but I found it to be a pretty neat tool to restrict viewing of the rest of the sheet.

Jodie

Andrée Starå

@Itasca User

Happy to help!

I saw that Jodie answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

✅Remember! Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Patty Vincent

@Andrée Starå

I would still like to understand how to use the Unique Identifier to allow a form to update the data. Are you suggesting something other than an Update Request? Thanks.

Patty Vincent

xSnipersGox

i haven't tried it myself. but the logic would be this.

Condition:

Master sheet row have unique ID

1) have a new sheet (update sheet), with form input feature, require user to enter the unique ID on new sheet

2) copy information to master sheet from new sheet using formula/automation

however, update request may be easier, if there is a way to "format" update request, then we would never do the other sheet methodology.

Paul Newcome

@xSnipersGox has the right idea.

You would have your master sheet. Then you would create a second sheet for updating. Make sure you capture the unique id with the form entry (and have new form entries populate at the top of the sheet and then use an INDEX/COLLECT on the master sheet with cross sheet references to pull the data in from the second sheet.

SalVieira3

@Andrée Starå

I would love a step-by-step breakdown of how to Update via form with the Unique Identifier? I see the answers folks have above, but I am newer to Smart Sheet and do not understand how to achieve this with the steps mentioned above - and the Help Articles relating to formula/automation aren't helping me to figure this out. Thanks!

John Reynolds

I tried to follow Jodie's advice, but got stuck. I tried to create the update request using her advice, but I got lost on the second to last line: “Click on the Kabob and choose Advanced Options.” I can’t find that as an option on the automation I am trying to set up:

Is there a step I am missing? I don’t want to send each supplier the entire sheet, just the information on their row. I’d appreciate any assistance. Thanks, John

Genevieve P.

Hi @John Reynolds

Alerts have been updated since the comment from 2020! Look down at the bottom where it asks you what you want the message to include. Here you can select Links to sheet and specific fields, meaning you can limit what column data is being sent out:

If the person receiving the email is not shared to the sheet, then even though the sheet link is present, it won't allow them to see the sheet. If they click that link it will let them know they don't have access.

Here's more information: Customize the content of your alerts and requests

Cheers,

Genevieve

John Reynolds

Hi, @Genevieve P. :

Thanks for responding. I do have the option for controlling columns. I was asking about accessing of rows, though. I want to be able to have notifications go out that show the service provider only information that is about their company, Thanks, John

Genevieve P.

Hi @John Reynolds

Thanks for clarifying!

If you set the alert to send to contacts in a cell, it will only send the rows with the email identified to that specific contact (containing the column/cell data you've specified under "specific fields").

Cheers,

Genevieve