1

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.

migrated from superuser.com Apr 10 at 11:23

This question came from our site for computer enthusiasts and power users.

0

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.

  • Thank you so much for responding. The trick to only show this for rows that have data is really nice. The only issue with this solution I think is that it doesn't work if a new form response is added (when someone submits another Google Form response). This creates a new row, and I can't figure out how to get this formula to appear in column E, even when new rows that are added to the spreadsheet. Is there any way around this issue that you know of? – Akira Apr 9 at 2:29
  • I'm glad that I could help. Ah I didn't think about the problem which adding rows like that. I think it can be done using a similar formula in a script. I'll have a quick play and see if I can get it. – GooberZoober Apr 9 at 2:31
  • I've wrote a script that I think works. I'll add it to the bottom of my answer and hope it works for you. – GooberZoober Apr 9 at 2:54
  • I changed the name of the function and added a trigger to it, in the hopes that it will fire when a new row is added. I haven't added the changes in the answer, but they are in the Google sheet. I hope it works now! – GooberZoober Apr 9 at 4:43
0
  • to find last value in a range:

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

    0

  • 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), )})

    0


demo spreadsheet

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy