Using forms to update current information in Sheets? Or another way to automate this process?

ocanel

I have a form that we use to onboard new users. In the form, we would fill out information such as: contact name, position, department, manager, etc. Now, I want to use this same form and create a logic that would go something like this: Is the user "Current" or "New"?. So then the form displays certain fields depending on the answer. I tried this in the form and worked as expected.

However, it created a new row (a new submission), and I realized that I'm not really changing the logic in the sheets, just in the forms. What I'm trying to do is that when a current user either moves to a different department or gets a promotion, I wanted to use the forms to make these updates on current users (current information in the sheets). Are there any solutions to this rather than making these changes manually?

Naeem Ejaz

• Trigger: When a form is submitted AND user = "Current"

• Condition: Match email/ID with existing row

• Action: Send Update Request to the person who owns that record (or to HR/admin)

• That update request will allow them to edit the existing row instead of creating a duplicate.

Kerry St. Thomas

Because forms are explicitly meant to collect new info, you can't use a form to UPDATE existing information. HOWEVER, you can collect information and use it to update info. Depends on how you want to deal with your data.

Option 1: Create the form that you did, but just use it with a sheet that is devoted to nothing but staging your data. If "New" is chosen, create an automation when the form submission is received (or on whatever cadence you decide) to move the row to your sheet that contains employee info. If "Existing" is chosen, use something like an Employee ID number or some unique identifier, in combination with Data Mesh to update the fields on your employee info sheet.

Option 2: Create your form BUT JUST FOR NEW EMPLOYEES. For EXISTING employees, use a tool like a Report (and sharing to the underlying sheet), a WorkApp (with a report to that underlying sheet), or Dynamic View to give people access to edit info on the underlying sheet.

Between the two, I recommend Option 2 - if you don't have any premium apps, you can do it - and you have one less sheet to deal with.

Good luck!

Naeem Ejaz

• Trigger: When a form is submitted AND user = "Current"

• Condition: Match email/ID with existing row

• Action: Send Update Request to the person who owns that record (or to HR/admin)

• That update request will allow them to edit the existing row instead of creating a duplicate.

Kerry St. Thomas

Because forms are explicitly meant to collect new info, you can't use a form to UPDATE existing information. HOWEVER, you can collect information and use it to update info. Depends on how you want to deal with your data.

Option 1: Create the form that you did, but just use it with a sheet that is devoted to nothing but staging your data. If "New" is chosen, create an automation when the form submission is received (or on whatever cadence you decide) to move the row to your sheet that contains employee info. If "Existing" is chosen, use something like an Employee ID number or some unique identifier, in combination with Data Mesh to update the fields on your employee info sheet.

Option 2: Create your form BUT JUST FOR NEW EMPLOYEES. For EXISTING employees, use a tool like a Report (and sharing to the underlying sheet), a WorkApp (with a report to that underlying sheet), or Dynamic View to give people access to edit info on the underlying sheet.

Between the two, I recommend Option 2 - if you don't have any premium apps, you can do it - and you have one less sheet to deal with.

Good luck!