Search code examples
pygsheets

How to use previous row value for vertically merged cells in google sheets while fetching using pygsheets?

I am trying to get the data in the merged cell Level 1 in all the smallest rows, or iterate over the rows created by the merged cells.

I am getting

[['Level 1', 'Level 2', 'Level 3'], ['', '', 'Level 3'], ['', '', 'Level 3']]

when I am fetching the data for the first 3 rows using get_all_values method.

The expectation is to get:

[['Level 1', 'Level 2', 'Level 3'], ['Level 1', 'Level 2', 'Level 3'], ['Level 1', 'Level 2', 'Level 3']]

instead.

Currently I am using dictionaries to hold the data using a lot of logic to figure out the missing column data. Is there a better way of flattening the merged cells to their respective rows?

Representation of google sheet with merged cells

Solution

  • Assuming your data begin at row #2, you can complete the column A by, in D2

    =ArrayFormula(if($C2:$C="",,lookup(row(A2:A),row(A2:A)/if(A2:A<>"",1,0),A2:A)))

    and to complete column B, in E2

    =ArrayFormula(if($C2:$C="",,lookup(row(B2:B),row(B2:B)/if(B2:B<>"",1,0),B2:B)))

    and in F2

    =ArrayFormula(if($C2:$C="",,C2:C))

    in this way you will get a complete set of data