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

Colab short_cut

 

Snippets in Google Colab

 A snippet is a reusable piece of code in Colab, which you can use whenever you want. The following instructs how to create a snippet in Colab.

1. create the name of a snippet with heading in a text cell. note: the name must be given a heading sign #, or ## etc., which assigns an address to the name
2. create the codes you want to reuse in the immediate next code cell
3. Go to Tools and open Settings:
   
   
   
4. Just paste the link of a notebook containing snippets, click SAVE, and you are ready to go:
   
   
5. You can see all your snippets here. Just search for your snippet by its snippet name and insert it into your code