How to pull last value in a Google Sheets column and populate it across another worksheet column?

Question

This is my first post here- I hope this is the right place for it, apologies in advance if it isn't.

Context:

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.

Question

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).

Please see this sample worksheet that I put together for reference, and feel free to edit it.

Answer 1

There are several ways of finding the data in the last cell of a column. I found several good solutions here on Stack Overflow.

I like the solution using the Index and CountA functions. The Index function returns the value in a cell when given a range and a position in the range, and the CountA function returns the number of cells with text in it, within a range.

In your example, the ranges for both the Index and the CountA function are B:B (which is the whole column of B). CountA will return the number of cells in column B with text in it, which will be the same as the row number of the last row. This assumes that there aren't any skipped rows in that column, which I think is acceptable for your problem. Then using the Index function and the result of the CountA function, the text in the cell can be found:

=INDEX(B:B,COUNTA(B:B),1)

To get this result on your 'User information' sheet, you need to specify the sheet name of were the data come from in the formula. In your case this would be:

=INDEX('Presentation password'!B:B,COUNTA('Presentation password'!B:B),1)

If you copy this formula into E2 and drag the cell to Autofill all the way to the bottom, the entire column will be filled with the most recent password.

To make this neater by showing the password only in rows that have user data entered into them, an if statement can be added to check if the first cell contains text:

=IF(A2<>"",INDEX('Presentation password'!B:B,COUNTA('Presentation password'!B:B),1),"")

This leaves cells in which the row doesn't have text in the first column blank.

EDIT:

As it was pointed out in the comments, this doesn't work when a row of cells is inserted. I think the only way this can be achieved is with a script. I created an onEdit function in a macro, and it seems to work for me. This is the script I wrote:

function onEdit(e) {

  var range = SpreadsheetApp.getActiveSpreadsheet().getRange("'User information'!E2:E");
  range.setFormula("=IF(A2<>\"\",INDEX('Presentation password'!B:B,COUNTA('Presentation password'!B:B),1),\"\")");

};

I'm not sure if this will work the way you need, because I think the spreadsheet needs to be active, and I'm not sure if the spreadsheet is open while you add data to it.

Answer 2

• to find last value in a range:

  =QUERY(B:B, "limit 1 offset "&COUNTA(B:B)-1)

  

• to repeat assign it to the users:

  =ARRAYFORMULA({"Current presentation password"; IF(LEN(A2:A), 
   QUERY('Presentation password'!B:B, 
   "limit 1 offset "&COUNTA('Presentation password'!B:B)-1), )})

  

---

demo spreadsheet