How to extract or save back data from or to google spreadsheet

 This post discusses how to extract or save back data from or to google spreadsheet with pandas in Colab

    1 import authorization libriary

 from google.colab import auth

    import gspread

    import gspread_dataframe as gd

    from oauth2client.client import GoogleCredentials

  

2 import pandas 

    import pandas as pd

3 connect Colab with spreadsheet, authorize Colab to manipulate spreadsheet

    auth.authenticate_user()

    gc = gspread.authorize(GoogleCredentials.get_application_default())

4 connect pandas with worksheet

    def get_sheet(URL, sheet_name):

         wb = gc.open_by_url(URL)

         sheet = wb.worksheet(sheet_name)

         return sheet

5 convert worksheet to DF (pandas dataFrame)

    def get_sheetDF(URL, sheet_name):

      sheet = get_sheet(URL, sheet_name)

      data = sheet.get_all_values()

      df = pd.DataFrame(data)

      return df

6 save pandas to worksheet

    gd.set_with_dataframe(sheet, sheetDF)

reference code