14_Integrando_Google_Colab_e_Google_Planilhas_(spreadsheet).ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "14 - Integrando Google Colab e Google Planilhas (spreadsheet).ipynb",
      "provenance": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ricospeloacaso/python_para_investimentos/blob/master/14_Integrando_Google_Colab_e_Google_Planilhas_(spreadsheet).ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_X9BhKltk_cb",
        "colab_type": "text"
      },
      "source": [
        "###Ricos pelo Acaso\n",
        "* Link para o vídeo: https://youtu.be/YaCeqXpxWUk"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xdIDjdI1Uq5Y",
        "colab_type": "text"
      },
      "source": [
        ">Instalando `gspread`"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8f78dBjnA3Y-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install gspread"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "40vthssCUv1X",
        "colab_type": "text"
      },
      "source": [
        ">Importando as Bibliotecas"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VgLSPFycA9SJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "from google.colab import auth\n",
        "import gspread\n",
        "from oauth2client.client import GoogleCredentials"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "R2du4Rvuxq4h",
        "colab_type": "text"
      },
      "source": [
        ">Realizando a Autenticação"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bJGFIHvJxcfo",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "auth.authenticate_user()\n",
        "gc = gspread.authorize(GoogleCredentials.get_application_default())"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SBpuhBm9U8cg",
        "colab_type": "text"
      },
      "source": [
        ">Criando uma planilha vazia"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PpVsOifeBB0m",
        "colab_type": "code",
        "outputId": "ec175888-ae1c-4d87-acb3-813f6beab574",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "gc.create('Planilha01')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<Spreadsheet 'Planilha01' id:19pRZ5GYD4cGGa-p2yoiuy05sKefT6w-XFUpi04jPlgE>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PLGoqsjliO8P",
        "colab_type": "text"
      },
      "source": [
        ">Abrindo uma Planilha"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WCxNYLZCiPBv",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "planilha = gc.open('Planilha01')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4qHcboXCVZ_j",
        "colab_type": "text"
      },
      "source": [
        ">Adicionando uma nova Página\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gSLxsA5sVfuR",
        "colab_type": "code",
        "outputId": "9fec4a45-58f6-4a39-be01-df732dead789",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "planilha.add_worksheet('nova_pagina', 10, 5)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<Worksheet 'nova_pagina' id:40256675>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UtRICcfNaMOq",
        "colab_type": "text"
      },
      "source": [
        ">Selecionando uma Página"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dGhngLzmhJO1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "pagina1 = planilha.get_worksheet(0)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hkWy8KOCi4py",
        "colab_type": "text"
      },
      "source": [
        ">Apagando uma Página"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6HvqjzJghZZH",
        "colab_type": "code",
        "outputId": "b118859d-0405-4385-973e-ff48182b7377",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 51
        }
      },
      "source": [
        "planilha.del_worksheet(pagina1)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'replies': [{}],\n",
              " 'spreadsheetId': '19pRZ5GYD4cGGa-p2yoiuy05sKefT6w-XFUpi04jPlgE'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4-B4RpqpaOu5",
        "colab_type": "text"
      },
      "source": [
        ">Alterando os valores das células"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "TkU-D5Zhkle4",
        "colab_type": "text"
      },
      "source": [
        ">>1º passo: selecionar a página"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BMNOIhJKjIQL",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "nova_pagina = planilha.get_worksheet(0)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Xx7MhtXaktQp",
        "colab_type": "text"
      },
      "source": [
        ">>`update_acell()`: pelo código da célula"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9w9LPmdRZtuQ",
        "colab_type": "code",
        "outputId": "60dc71a4-0f2a-4c78-c16d-a168e6436a1e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 102
        }
      },
      "source": [
        "nova_pagina.update_acell('a1', 120)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '19pRZ5GYD4cGGa-p2yoiuy05sKefT6w-XFUpi04jPlgE',\n",
              " 'updatedCells': 1,\n",
              " 'updatedColumns': 1,\n",
              " 'updatedRange': 'nova_pagina!A1',\n",
              " 'updatedRows': 1}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fr1mLethlDIF",
        "colab_type": "text"
      },
      "source": [
        ">>`update_cell`: pelos números da linha e coluna"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1eU9gm3qZtya",
        "colab_type": "code",
        "outputId": "c8872f71-dab5-4411-b07d-a9dbd03721e8",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 102
        }
      },
      "source": [
        "nova_pagina.update_cell(2,2, 'qualquer texto')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '19pRZ5GYD4cGGa-p2yoiuy05sKefT6w-XFUpi04jPlgE',\n",
              " 'updatedCells': 1,\n",
              " 'updatedColumns': 1,\n",
              " 'updatedRange': 'nova_pagina!B2',\n",
              " 'updatedRows': 1}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 12
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tldCxUqWlQBc",
        "colab_type": "text"
      },
      "source": [
        ">>`update_cells()`: por uma lista de valores"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ME3vC5O8bgR9",
        "colab_type": "code",
        "outputId": "2c0ca856-00d5-45d9-88cb-356166f8e6f6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 85
        }
      },
      "source": [
        "lista_cel = nova_pagina.range('a1:b2')\n",
        "lista_cel"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[<Cell R1C1 '120'>,\n",
              " <Cell R1C2 ''>,\n",
              " <Cell R2C1 ''>,\n",
              " <Cell R2C2 'qualquer texto'>]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fDF-oFOznejR",
        "colab_type": "code",
        "outputId": "e13d8237-f8c4-46dd-884f-532e0b79836d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "valores = ['a','b','c','d']\n",
        "\n",
        "for i, valor in enumerate(valores):\n",
        "  lista_cel[i].value = valor\n",
        "\n",
        "lista_cel"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[<Cell R1C1 'a'>, <Cell R1C2 'b'>, <Cell R2C1 'c'>, <Cell R2C2 'd'>]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 17
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "litmy4_xnGKx",
        "colab_type": "code",
        "outputId": "3e8f646c-1194-40c8-8fcb-390818279f3b",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 102
        }
      },
      "source": [
        "nova_pagina.update_cells(lista_cel)"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '19pRZ5GYD4cGGa-p2yoiuy05sKefT6w-XFUpi04jPlgE',\n",
              " 'updatedCells': 4,\n",
              " 'updatedColumns': 2,\n",
              " 'updatedRange': 'nova_pagina!A1:B2',\n",
              " 'updatedRows': 2}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 18
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_CqDZr8LAj-3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}