1

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

codehia
  • 124
  • 2
  • 4
  • 16

1 Answers1

1

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

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • I should have been a little more precise in the question, I was trying to find a way to get the data using pygsheets without modifying the google sheet in question. I have edited the question +1 for this answer.. – codehia Aug 05 '21 at 12:33