I am currently an intern working on a code to pull data from a google spreadsheet and create a DITA file from that data. One problem I found is that the .row_count method counts ALL the rows in a spreadsheet; I only need to count a range of rows up to (and including) the last filled row in the spreadsheet.
I tried deleting the extra rows in the spreadsheet but that is not a feasible solution to count the number of rows.
rowCount = sheet.row_count
paragraph = ET.SubElement(conbody, "p")
table = ET.SubElement(conbody, "table")
tgroup = ET.SubElement(table, "tgroup", attrib={"cols":
str(rowCount)})
tbody = ET.SubElement(tgroup, "tbody")
i = 0
while i < rowCount:
row = ET.SubElement(tbody,"row")
i += 1
When I run my while loop, I end up getting 1001 rows, when I actually need a range of rows that do not contain all of the empty rows after the last filled row. For example, I have a google sheets with data filled in the 1st, 2nd, and 4th rows, the 3rd row is empty. However, I need to count the number rows from the first filled row with data to the last row filled with data, even if rows in between are empty.
I might have to make a function with if statements but I have no idea where I would start with the google sheets api.
I found a solution to my problem from a post by @rdt0086. The solution to my problem is to get all the data and count it:
rowCount = len(sheet.get_all_values()) # this is a list of list of all data and the length is equal to the number of rows including header row if it exists in data set
What this seems to do, is exactly what I was looking for: it counts a range of rows from the first row with filled data (the header row) to the last row with filled data.