How can we help?

New post
2

How to set a permanent column from google drive

Avatar
Oscar Rocha
None
Follow

Hey there

does any one know how can we set a column from spreadsheet of google drive like a permanent match so if we add columns behind thi "permanente" one, the formulas keep link to it and not get an empty value.. so i need to re fix a position because of this

i was thinking on adding a header id and use a lookup to search for the begining of the column no matter where it is or had been moved, but seems that the formula will be like a switch and a bit large.

 

any answer of it or idea will be good

Date Votes

5 comments

  • Avatar
    Josh Cohen-Collier Official comment

    Hi Oscar,

    Thanks for posting in our community.
    The method to do this involves using the DATASOURCE function to point to a column with a specific header in your data.
    You will point to the datasource you are looking for, and find the column(which you must manually enter in an ARRAY), where the header row is equal to the row you are looking for.

    If you are pointing to a datasource with multiple sheets, you will need to use the CONCAT function to add this to the beginning.

    Hope this helps!

    Best regards,
    Joshua




  • 0
    Avatar
    Oscar Rocha

    thankyou for the quick answer, im very happy about it.

    its exactly what i was looking for, didn't know about the use of the datasource formula combined with the header array

    just have the logical idea on how it should work, ill will reestrcturate my formulas in this way!

  • 1
    Avatar
    Josh Cohen-Collier

    Oscar,

    No problem, happy to help!

    Joshua

  • 0
    Avatar
    Oscar Rocha

    It's working so good, thanks so much im having new ideas by this formula helping.

    one more question related

    do you have any tip to replace the ARRAY of column letter names for something more dynamic

    ARRAY("A:A","B:B","C:C","D:D","E:E","F:F","G:G","H:H","I:I","J:J","K:K","L:L", ...  )

    Like obtain the lenght of columns with values or at least the columns you have to generate like a script to make this "A:A", "B:B", "C:C".. order and format ""  like a while or for bucle until the lenght

    its because what will be happend if we add a new column in somewhere of the file, adding the same format with a header id to call it.  The array will be missing one column, so the last column value will make an error.

    Also i was thiking on adding a new column on A:A and on each cell ill add the values ( "A:A", "B:B", "C:C" with or without the "" not sure yet)  and when i add a new column i add the extra cell value with the name letter of the new column   xD  

    LOL  a bit triccky this kind of solution but will be better than replace on each formula a new column on the array.

     

     

     

  • 1
    Avatar
    Josh Cohen-Collier

    Hi Oscar,

    Glad to hear this is working!
    Unfortunately there is no way of getting the values using the ARRAY function using a script, they do indeed need to be hard-coded.

    However, your solution of putting the column names in the datasource as a row is brilliant!
    This would replace the ARRAY function.

    In the above case, we assume that row 1 contains the column names(A:A, B:B, etc), and row 2 is the column headers(eg 'Email', 'Address', etc).

    Best regards,
    Joshua

Please sign in to leave a comment.