Sunday, 18 April 2021

How to use .find() to locate a cell and start updating cells in rows below in loop?

I'm automating filling out a Google Sheet with data taken from CSV. For the automation, I want to be able to use .find() to locate a specific cell value that is used as a reference for where to start updating cells. To better explain:

enter image description here

My code uses .find('Cafe Crepe') to locate the rows and columns belonging to the restaurant 'Cafe Crepe'. In the sheet there are multiple restaurants with the same format for orders, sub total, etc. beneath.

def matchAndWriteFinalCSV(self, sheet, restaurant):
        '''
        Match orders from Ecwid csv to restaurant in Delivery csv
        Write to Final csv
        '''
        print("WRITE START")
        cell = sheet.find(f"{restaurant}")
        filtered_list = [] 
        print("WRITE SHEET")
        print(f"ROW {cell.row} COL {cell.col} CELL {cell}")
        sheet.update('R5', "TEST")

UPDATE

To illustrate what the result should be:

enter image description here

I decided to go for creating a list of dictionaries of orders(order num, sub total, tx, etc). Using a for loop, I divide the task of writing/updating the google sheet by restaurant. In the example for this question: my code takes all orders belonging to 'Cafe Crepe' and initates to write/update the order #, sub total, tax, etc. fields.

for rest in self.filtered_list:
    restaurant = rest['restaurant'] 
    cell = sheet.find(f"{restaurant}") 
    print("WRITE SHEET")
    print(f"ROW {cell.row} COL {cell.col} CELL {cell}")
    new_row = cell.row + 5 
    write_cell_start = f"R{str(new_row)}C{str(cell.col)}"

I can locate the first order # cell with new_row = cell.row + 5 , but I can't figure out how to use this to write/update cells for sub total, tax, etc, AND to then continue on the next row for the next order. How can I achieve this in a loop?

Would it be better to use batch update for this job?



from How to use .find() to locate a cell and start updating cells in rows below in loop?

No comments:

Post a Comment