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:

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:

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 restaurant_name, restaurant_orders in orders_per_restaurant.items():
            new_row = 5
            for order in restaurant_orders:
                print(order)
                restaurant = restaurant_name
                cell = sheet.find(f"{restaurant}") 
                print(f"ROW {cell.row} COL {cell.col} CELL {cell}") 
                subtotal = cell.col + 1 
                tax = cell.col + 2
                new_cellrow = cell.row + new_row
                write_cell_start = f"R{str(new_cellrow)}C{str(cell.col)}"
                write_subtotal = f"R{str(new_cellrow)}C{str(subtotal)}"
                sheet.update(write_cell_start, order['order'])
                sheet.update(write_subtotal, order['sub-total'])
                new_row += 1
            new_row =  5
            time.sleep(100)

This works but now I can't get this code to run beyond a certain point without getting "Quota exceeded for quota metric 'Write requests' and limit 'Write requests per minute per user' of service 'sheets.googleapis.com'. I'm trying to understand how I can achieve the same thing with batch_update(). How can I work around exceeding the request per minute rate in my code?

Recommended topics

Hot tags