This is my first post here- I hope this is the right place for it, apologies in advance if it isn't.
I have a password protected presentation that I need to give users access to, but only after they have submitted their information to me via a Google Form. There is only a single password that is used to access the presentation, and administrators will want to update it every now and then.
I created a Google Spreadsheet with two worksheets in it that I've set up to accept data from two separate Google Forms. The worksheet "User information" stores user data, the other worksheet "Presentation password" stores the latest password to the presentation (each row in this spreadsheet contains a password that was used to access the presentation, and only the most recent row is the current password).
I've set up a Zapier "Zap" (automation) to the Google Form so that when a new user submits their information to gain access to the presentation, an email is automatically sent to the user with the link to the presentation, which is hosted online. I want to be able to send the most current presentation password to the user in this email, and in order to do this, I need to find a way to constantly and automatically pull over the most recent password from the "Presentation password" worksheet into a column in the "User information" worksheet, even as new rows are added to "Presentation password".
A separate Google Form used by administrators of this presentation is used to add a new row to the "Presentation password" worksheet. I want them to use this Google Form as an easy way to update the password that appears in the Zapier email.
Whenever a new password is submitted to the "Presentation password" worksheet, a new row is added below the most recent data. I need to pull the password in the last row of password data and to automatically replicate it across all rows in column F of the "User information" worksheet (or at least, to all rows that have user information in them). My hope is that by doing this, Zapier (which can only populate emails with data from a single worksheet) will have the data that it needs to email users with the latest password data, and administrators of this presentation will be able to use a Google Form to update the password that appears in this email, rather than having to log in to Zapier to manually type it in.
Is what I'm trying to do possible? I've tried some array formula and index/count stuff that I found while searching around, but wasn't able to get any of them to do exactly what I'm looking for (the array formula that I set up with index/count didn't automatically populate across the entire column, or for new rows that are added when new data is entered).