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

Viewed 260

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 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?

1 Answers

This should steer you where you want to go:

iRow = Sheet.cells.find("searchString").Row
iCol = Sheet.cells.find("searchString").Column

newCell = Sheet.cells(iRow,iCol).offset(iiRow, iiCol)

iiRow and iiCol can be iterators you define in your new loops that use iRow, iCol as their baseline reference point.

Related