Search code examples
looker-studio

Link two cells in google sheets with autofill

First of all I would like to thank you for your time.

I have a google data studio report that extracts data from a google sheet. The data studio sheet gets values from a google form (in the form of another tab in the sheet). Altough the cells are linked, right now I have to drag the cells in the data studio sheet to pull the values from the forms sheet. If there are no values it can´t pull anything and I would like to have real time values in the google data studio as soon as a form is filled.

Right now all I have is a simple (='Form '!C55) to pull. What I would like to do is if there is a new value in the following cell in the forms sheets then the following cell in the data studio sheets pulls it so it can go to the report in dat studio.

Cheers to all!

Solution

  • Try this formula, in column A, after your last row of good data. So perhaps in Dados!A91. Note you will need to first delete everything in all of the cells below and to the right of A91, since this formula is filling everything:

    =QUERY('Formulário '!A9:O;"select A,G,D,J,M,H,E,K,N,I,F,L,O where B <> '' ";0)

    This queries your Formulário sheet, and pulls all of the data starting in row 9 (since that is what you were showing with your formula before), and selects all of the correct columns in order.

    Please test it out with a test form submission, to see that it works as expected, and that it is copying the correct columns, in the right order. Let me know of any questions or issues.

    I'm not positive how sheet updates work when there is no active user logged into the sheet, but I suppose when Data Studio goes to pull from Dados, it will first ensure that it has the latest data from all formulas.

    enter image description here

    Update

    To have the Max and Min values,which you say should be the same all the way down the column, add a formula like the following in the header row (row 1) of your Formulario sheet:

    ={"Cloro Max.";ArrayFormula(IF(LEN(A2:A);1,5;""))}

    That gives a value of 1,5 for a column labelled Cloro Max. Be sure to delete anything from row 2 down, in that same column, or the array formula gives a #REF error, since it can't put data when there is already data entered in those lower cells.

    You can change the text to create a Max or Min column for each value you want, in columns Q to V. Change the 1,5 to whatever number you want, such as 0,5 for Cloro Min.

    It will always add the value(s) to each new row as it gets added from a submitted form response.