How to save the output into google sheets

[Hritikm28]

Hi team,

I am new to python and was trying to solve the vehicle routing problem using google OR tools. The code works fine for me but I have a silly issue. I don't know how to save this into google Sheets.
I am using the below code

from __future__ import print_function
### Install The Libraries
!pip install ortools
!pip install --upgrade gspread

from ortools.constraint_solver import routing_enums_pb2
from ortools.constraint_solver import pywrapcp

## Read the Data

from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()

gc = gspread.authorize(creds)

wb = gc.open_by_key('Google Sheet ID')

worksheet = wb.worksheet('Input_Colab')

# get_all_values gives a list of rows.
rows = worksheet.get_all_values()

# Convert to a DataFrame and render.
import pandas as pd
df = pd.DataFrame.from_records(rows[1:],columns=rows[0])
pd.set_option("display.max_columns",None)

import ast

dm = ast.literal_eval(df.iloc[0]['time_matrix'])
dw = ast.literal_eval(df.iloc[0]['time_windows'])
py = ast.literal_eval(df.iloc[0]['Payload'])
vn = ast.literal_eval(df.iloc[0]['No of Vehicles'])
vc = ast.literal_eval(df.iloc[0]['Vehicle Capacity'])

time_matrix = dm

time_windows = dw

demands = py

num_vehicles = vn
vehicle_capacities = vc

depot_index = 0
time_limit_seconds = 1000

def create_data_model(time_matrix, time_windows, num_vehicles, demands, vehicle_capacities, depot_index):
    """Stores the data for the problem."""
    data = {}
    data['time_matrix'] = time_matrix
    data['time_windows'] = time_windows
    data['num_vehicles'] = num_vehicles
    data['demands'] = demands
    data['vehicle_capacities'] = vehicle_capacities
    data['depot'] = depot_index
    return data

"""Capacitated Vehicles Routing Problem (CVRP) with Time Windows."""

def print_solution(data, manager, routing, solution):
    """Prints solution on console."""
    total_distance = 0
    total_load = 0
    time_dimension = routing.GetDimensionOrDie('Time')
    total_time = 0
    
    for vehicle_id in range(data['num_vehicles']):
        index = routing.Start(vehicle_id)
        plan_output = 'Route for vehicle {}:\n'.format(vehicle_id)
        route_load = 0
        while not routing.IsEnd(index):
            node_index = manager.IndexToNode(index)
            time_var = time_dimension.CumulVar(index)
            route_load += data['demands'][node_index]
            plan_output += 'Place {0:>2} Arrive at {2:>2}min Depart at {3:>2}min (Load {1:>2})\n'.format(manager.IndexToNode(index), route_load, solution.Min(time_var), solution.Max(time_var))
            
            previous_index = index
            index = solution.Value(routing.NextVar(index))
            
            
        time_var = time_dimension.CumulVar(index)
        total_time += solution.Min(time_var)
        plan_output +="Place {0:>2} Arrive at {2:>2}min \n\n".format(manager.IndexToNode(index), route_load, solution.Min(time_var), solution.Max(time_var))
        
        # route output
        plan_output += 'Load of the route: {}\n'.format(route_load)
        plan_output += 'Time of the route: {}min\n'.format(solution.Min(time_var))
        plan_output += "--------------------"
        
        print(plan_output)
        total_load += route_load

    print('Total load of all routes: {}'.format(total_load))
    print('Total time of all routes: {}min'.format(total_time))

    
def main():
    """Solve the VRP with time windows."""
    # Instantiate the data problem.
    data = create_data_model(time_matrix, time_windows, num_vehicles, demands, vehicle_capacities, depot_index)

    # Create the routing index manager.
    manager = pywrapcp.RoutingIndexManager(len(data['time_matrix']),
                                           data['num_vehicles'], data['depot'])

    # Create Routing Model.
    routing = pywrapcp.RoutingModel(manager)


    # Create and register a transit callback.
    def time_callback(from_index, to_index):
        """Returns the travel time between the two nodes."""
        # Convert from routing variable Index to time matrix NodeIndex.
        from_node = manager.IndexToNode(from_index)
        to_node = manager.IndexToNode(to_index)
        return data['time_matrix'][from_node][to_node]

    transit_callback_index = routing.RegisterTransitCallback(time_callback)

    # Define cost of each arc.
    routing.SetArcCostEvaluatorOfAllVehicles(transit_callback_index)
    
    # Add Capacity constraint.
    def demand_callback(from_index):
        """Returns the demand of the node."""
        # Convert from routing variable Index to demands NodeIndex.
        from_node = manager.IndexToNode(from_index)
        return data['demands'][from_node]

    demand_callback_index = routing.RegisterUnaryTransitCallback(
        demand_callback)
    routing.AddDimensionWithVehicleCapacity(
        demand_callback_index,
        0,  # null capacity slack
        data['vehicle_capacities'],  # vehicle maximum capacities
        True,  # start cumul to zero
        'Capacity')
    
    # Add Time Windows constraint.
    time = 'Time'
    routing.AddDimension(
        transit_callback_index,
        5,  # allow waiting time
        480,  # maximum time per vehicle
        False,  # Don't force start cumul to zero.
        time)
    time_dimension = routing.GetDimensionOrDie(time)
    
    # Add time window constraints for each location except depot.
    for location_idx, time_window in enumerate(data['time_windows']):
        if location_idx == 0:
            continue
        index = manager.NodeToIndex(location_idx)
        time_dimension.CumulVar(index).SetRange(time_window[0], time_window[1])

    # Add time window constraints for each vehicle start node.
    for vehicle_id in range(data['num_vehicles']):
        index = routing.Start(vehicle_id)
        time_dimension.CumulVar(index).SetRange(data['time_windows'][0][0],
                                                data['time_windows'][0][1])

    # Instantiate route start and end times to produce feasible times.
    for i in range(data['num_vehicles']):
        routing.AddVariableMinimizedByFinalizer(
            time_dimension.CumulVar(routing.Start(i)))
        routing.AddVariableMinimizedByFinalizer(
            time_dimension.CumulVar(routing.End(i)))
    # Allow to drop nodes.
    penalty = 1000
    for node in range(1, len(data['time_matrix'])):
        routing.AddDisjunction([manager.NodeToIndex(node)], penalty)

    # Setting first solution heuristic.
    search_parameters = pywrapcp.DefaultRoutingSearchParameters()
    search_parameters.first_solution_strategy = (
        routing_enums_pb2.FirstSolutionStrategy.PATH_CHEAPEST_ARC)
    search_parameters.time_limit.seconds = time_limit_seconds
    
    # Solve the problem.
    solution = routing.SolveWithParameters(search_parameters)

    # Print solution on console.
    if solution:
        print_solution(data, manager, routing, solution)
    return solution

solution = main()

[lperron]

Please indicate when crossposting from stack overflow

[Hritikm28]

Sure.
I posted it to get help quickly but unfortunately unable to get the answer from any of the websites. Can you help??

[lperron]

No. I think you can look at https://developers.google.com/sheets/api/quickstart/python Laurent Perron | Operations Research | ***@***.*** | (33) 1 42 68 53 00 Le mar. 27 sept. 2022 à 07:15, Hritikm28 ***@***.***> a écrit :

…

Sure. I posted it to get help quickly but unfortunately unable to get the answer from any of the websites. Can you help?? — Reply to this email directly, view it on GitHub <#3470 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACUPL3OBMPLWSGBKCXBTSS3WAJ7HPANCNFSM6AAAAAAQUR3WCE> . You are receiving this because you commented.Message ID: ***@***.***>

[Hritikm28]

Thanks