Google_Drive_101.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Google_Drive_101.ipynb",
      "provenance": [],
      "authorship_tag": "ABX9TyPEyONTu+i2Gb84L3VJSo3v",
      "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/fonsecajr/Projects/blob/master/Google_Drive_101.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "D617HpgHZR6L",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 135
        },
        "outputId": "ae0ace01-f795-498e-ca31-f3db9e1b29b2"
      },
      "source": [
        "!pip install gspread"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: gspread in /usr/local/lib/python3.6/dist-packages (3.0.1)\n",
            "Requirement already satisfied: requests>=2.2.1 in /usr/local/lib/python3.6/dist-packages (from gspread) (2.23.0)\n",
            "Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.6/dist-packages (from requests>=2.2.1->gspread) (3.0.4)\n",
            "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.6/dist-packages (from requests>=2.2.1->gspread) (2020.4.5.1)\n",
            "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.6/dist-packages (from requests>=2.2.1->gspread) (1.24.3)\n",
            "Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.6/dist-packages (from requests>=2.2.1->gspread) (2.9)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aUC6nEh6Z-Lg",
        "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": "code",
      "metadata": {
        "id": "Xmu9RZEqaZ49",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# Processo de Autenticação\n",
        "auth.authenticate_user()\n",
        "gd = gspread.authorize(GoogleCredentials.get_application_default())"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zeDTc4zVb4nv",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        },
        "outputId": "a5cf7a03-2cbf-4560-c639-7d938778989a"
      },
      "source": [
        "gd.create('SS_101') # Para criar a planilha"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<Spreadsheet 'SS_101' id:1eFPIj8_t43SbwHXWamYDo96Lt1FrmMNGrpMGJaErqSU>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 23
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9q3ck2D0c4nQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "planilha = gd.open('SS_101') # Para abrir a planilha"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x67usvFQdPk9",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        },
        "outputId": "38140d8d-922a-4585-fb2d-fd8bd0a97033"
      },
      "source": [
        "planilha.add_worksheet('Page_2', 100, 10) # Para criar uma nova folha"
      ],
      "execution_count": 25,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "<Worksheet 'Second' id:448844418>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 25
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xieUQfw-deFj",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "page_2 = planilha.get_worksheet(1) # Para selecionar a página criada"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0v8_8PZ6dv9H",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 50
        },
        "outputId": "6c56e55f-a4b1-4a2e-ff97-2308fcd74e9d"
      },
      "source": [
        "planilha.del_worksheet(page_2) # Para deletar uma folha. ! O valor não é Case Sensitive."
      ],
      "execution_count": 31,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'replies': [{}],\n",
              " 'spreadsheetId': '1eFPIj8_t43SbwHXWamYDo96Lt1FrmMNGrpMGJaErqSU'}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 31
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5r5Jn4cyfSRg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "page_1 = planilha.get_worksheet(0)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ApfO2fBfhggY",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 98
        },
        "outputId": "6a5e8d15-7e9e-436c-f234-6a3c57e5d3d1"
      },
      "source": [
        "page_1.update_acell('A1', 'Hello World')"
      ],
      "execution_count": 33,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '1eFPIj8_t43SbwHXWamYDo96Lt1FrmMNGrpMGJaErqSU',\n",
              " 'updatedCells': 1,\n",
              " 'updatedColumns': 1,\n",
              " 'updatedRange': \"'Página1'!A1\",\n",
              " 'updatedRows': 1}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 33
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iNSkqGIOhwos",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 98
        },
        "outputId": "ef7d6b23-c6c6-430a-e4d4-25e5ae0c9045"
      },
      "source": [
        "page_1.update_cell(1,2, 'Hello World 2')"
      ],
      "execution_count": 35,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '1eFPIj8_t43SbwHXWamYDo96Lt1FrmMNGrpMGJaErqSU',\n",
              " 'updatedCells': 1,\n",
              " 'updatedColumns': 1,\n",
              " 'updatedRange': \"'Página1'!B1\",\n",
              " 'updatedRows': 1}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 35
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "p_jfzEJ9iFtx",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 862
        },
        "outputId": "407b8313-5570-4bcc-f2f0-b30b3f9c705d"
      },
      "source": [
        "lista_on_page_1 =  page_1.range('a1:b26')\n",
        "lista_on_page_1"
      ],
      "execution_count": 48,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[<Cell R1C1 'Hello World'>,\n",
              " <Cell R1C2 'Hello World 2'>,\n",
              " <Cell R2C1 ''>,\n",
              " <Cell R2C2 ''>,\n",
              " <Cell R3C1 ''>,\n",
              " <Cell R3C2 ''>,\n",
              " <Cell R4C1 ''>,\n",
              " <Cell R4C2 ''>,\n",
              " <Cell R5C1 ''>,\n",
              " <Cell R5C2 ''>,\n",
              " <Cell R6C1 ''>,\n",
              " <Cell R6C2 ''>,\n",
              " <Cell R7C1 ''>,\n",
              " <Cell R7C2 ''>,\n",
              " <Cell R8C1 ''>,\n",
              " <Cell R8C2 ''>,\n",
              " <Cell R9C1 ''>,\n",
              " <Cell R9C2 ''>,\n",
              " <Cell R10C1 ''>,\n",
              " <Cell R10C2 ''>,\n",
              " <Cell R11C1 ''>,\n",
              " <Cell R11C2 ''>,\n",
              " <Cell R12C1 ''>,\n",
              " <Cell R12C2 ''>,\n",
              " <Cell R13C1 ''>,\n",
              " <Cell R13C2 ''>,\n",
              " <Cell R14C1 ''>,\n",
              " <Cell R14C2 ''>,\n",
              " <Cell R15C1 ''>,\n",
              " <Cell R15C2 ''>,\n",
              " <Cell R16C1 ''>,\n",
              " <Cell R16C2 ''>,\n",
              " <Cell R17C1 ''>,\n",
              " <Cell R17C2 ''>,\n",
              " <Cell R18C1 ''>,\n",
              " <Cell R18C2 ''>,\n",
              " <Cell R19C1 ''>,\n",
              " <Cell R19C2 ''>,\n",
              " <Cell R20C1 ''>,\n",
              " <Cell R20C2 ''>,\n",
              " <Cell R21C1 ''>,\n",
              " <Cell R21C2 ''>,\n",
              " <Cell R22C1 ''>,\n",
              " <Cell R22C2 ''>,\n",
              " <Cell R23C1 ''>,\n",
              " <Cell R23C2 ''>,\n",
              " <Cell R24C1 ''>,\n",
              " <Cell R24C2 ''>,\n",
              " <Cell R25C1 ''>,\n",
              " <Cell R25C2 ''>,\n",
              " <Cell R26C1 ''>,\n",
              " <Cell R26C2 ''>]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5A1Bmt58iVuv",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "71762c0d-0616-4bb1-e8a7-437660c330a8"
      },
      "source": [
        "values_to_input = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'x', 'z'] # Lembre-se de criar campos disponíveis.\n",
        "\n",
        "for i, valor in enumerate(values_to_input):\n",
        "  lista_on_page_1[i].value = valor\n",
        "\n",
        "values_to_input, lista_on_page_1 # Valores para inserir e a Lista criada para o Update"
      ],
      "execution_count": 49,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(['a',\n",
              "  'b',\n",
              "  'c',\n",
              "  'd',\n",
              "  'e',\n",
              "  'f',\n",
              "  'g',\n",
              "  'h',\n",
              "  'i',\n",
              "  'j',\n",
              "  'l',\n",
              "  'm',\n",
              "  'n',\n",
              "  'o',\n",
              "  'p',\n",
              "  'q',\n",
              "  'r',\n",
              "  's',\n",
              "  't',\n",
              "  'u',\n",
              "  'v',\n",
              "  'x',\n",
              "  'z'],\n",
              " [<Cell R1C1 'a'>,\n",
              "  <Cell R1C2 'b'>,\n",
              "  <Cell R2C1 'c'>,\n",
              "  <Cell R2C2 'd'>,\n",
              "  <Cell R3C1 'e'>,\n",
              "  <Cell R3C2 'f'>,\n",
              "  <Cell R4C1 'g'>,\n",
              "  <Cell R4C2 'h'>,\n",
              "  <Cell R5C1 'i'>,\n",
              "  <Cell R5C2 'j'>,\n",
              "  <Cell R6C1 'l'>,\n",
              "  <Cell R6C2 'm'>,\n",
              "  <Cell R7C1 'n'>,\n",
              "  <Cell R7C2 'o'>,\n",
              "  <Cell R8C1 'p'>,\n",
              "  <Cell R8C2 'q'>,\n",
              "  <Cell R9C1 'r'>,\n",
              "  <Cell R9C2 's'>,\n",
              "  <Cell R10C1 't'>,\n",
              "  <Cell R10C2 'u'>,\n",
              "  <Cell R11C1 'v'>,\n",
              "  <Cell R11C2 'x'>,\n",
              "  <Cell R12C1 'z'>,\n",
              "  <Cell R12C2 ''>,\n",
              "  <Cell R13C1 ''>,\n",
              "  <Cell R13C2 ''>,\n",
              "  <Cell R14C1 ''>,\n",
              "  <Cell R14C2 ''>,\n",
              "  <Cell R15C1 ''>,\n",
              "  <Cell R15C2 ''>,\n",
              "  <Cell R16C1 ''>,\n",
              "  <Cell R16C2 ''>,\n",
              "  <Cell R17C1 ''>,\n",
              "  <Cell R17C2 ''>,\n",
              "  <Cell R18C1 ''>,\n",
              "  <Cell R18C2 ''>,\n",
              "  <Cell R19C1 ''>,\n",
              "  <Cell R19C2 ''>,\n",
              "  <Cell R20C1 ''>,\n",
              "  <Cell R20C2 ''>,\n",
              "  <Cell R21C1 ''>,\n",
              "  <Cell R21C2 ''>,\n",
              "  <Cell R22C1 ''>,\n",
              "  <Cell R22C2 ''>,\n",
              "  <Cell R23C1 ''>,\n",
              "  <Cell R23C2 ''>,\n",
              "  <Cell R24C1 ''>,\n",
              "  <Cell R24C2 ''>,\n",
              "  <Cell R25C1 ''>,\n",
              "  <Cell R25C2 ''>,\n",
              "  <Cell R26C1 ''>,\n",
              "  <Cell R26C2 ''>])"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8A1KFUjHkkXF",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 98
        },
        "outputId": "5b4486bb-45ab-415d-9a01-5d280044d901"
      },
      "source": [
        "page_1.update_cells(lista_on_page_1)"
      ],
      "execution_count": 51,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "{'spreadsheetId': '1eFPIj8_t43SbwHXWamYDo96Lt1FrmMNGrpMGJaErqSU',\n",
              " 'updatedCells': 52,\n",
              " 'updatedColumns': 2,\n",
              " 'updatedRange': \"'Página1'!A1:B26\",\n",
              " 'updatedRows': 26}"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 51
        }
      ]
    }
  ]
}