Python日記

【GAS】Pythonでスプレッドシートの文字検索と、シートに転記(コピー)する方法【Google Sheets API】

2021年5月28日

GAS スプレッドシートの文字列を転機の巻
お悩みさん

Pythonでスプレッドシートの転記作業を実行したい!

Pythonとスプレッドシートを連携させることで、さまざまな業務を自動化できます。

例えば、スプレッドシートの必要な情報だけを抽出して、別のシートに転記(コピー)する場合、

元データの量が多ければ多いほど、膨大な時間がかかってしまいますが、

Pythonで実行すれば、一瞬で作業が完了できます。

そこで今回は、Pythonとスプレッドシート連携の練習もかねて、

スプレッドシートの内容(特定の文字列)を検索して、別シートに転記(コピー)」するという課題にチャレンジしてみます。

クリワン

この記事はこんな方におすすめです!

  • ノンプログラマーだけど、GASを使えるようになりたい
  • マーケティング業務でデータ解析をしていきたい
  • ITスキルを身につけて、年収をアップさせたい

この記事で学べること

  1. Google Sheets APIの使い方
  2. Pythonでスプレッドシートの文字列を検索する方法
  3. Pythonでスプレッドシートの文字列を別シートに転記する方法

環境

OSMac 10.15.4
Pythonpython 3.9
仮想環境Poetry
Homebrew8.0.19 Homebrew

Pythonとスプレッドシートの連携方法

今回は、Google Sheets APIの取得方法は割愛しますが。

Pythonとスプレッドシートの連携方法は以下の記事で詳しく解説していますので、そちらを参考にしてください。

元スプレッドシートを作成

Gmailに届いたメルマガタイトルをスプレッドシートに転記して、簡単な表を作成しました。(内容に特別な意味はありません)

今回は、シート1 に記載のある「楽天」の文字列を検索して、

シート2 への転記を試してみます。

つまり、下線のあるB6のセルがシートに2に転記できればOKです。

GAS GoogleSheetsAPI  スプレッドシート gmail_test1 「楽天」の文字列を検索して、転記

Pythonスクリプトの作成

Pythonとスプレッドシート連携の設定をした上で、以下の記述を作成します。

import gspread
from oauth2client.service_account import ServiceAccountCredentials


scope = ["https://spreadsheets.google.com/feeds","https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json',scope)
client = gspread.authorize(creds)


#シートの選択
sh = client.open_by_key('xxxxxxxxxxxxxxxxxxxxxxxx')
worksheet1 = sh.sheet1
worksheet2 = sh.worksheet('sheet2')

xxxxxxxxxxxxxxxxの部分には、ご自身のシートのKeyが入ります。
【GAS】Pythonでスプレッドシートに権限を付与する方法【Google Sheets APIの始め方】
Pythonのスクリプト作成の項目で紹介しています。

シート2を指定するときの注意点

ここでは、スプレッドシートのシート名を以下にしています。

GAS GoogleSheetsAPI  スプレッドシート gmail_test1 シート名 sheet1 sheet2

スクリプト上では、sheet2を以下の記述で指定していますので、

シート2の名称をsheet2にするか、以下のコードのsheet2の部分を適宜変更してください。

worksheet2 = sh.worksheet('sheet2')

スプレッドシートの文字列を検索する方法

ここでは、sheet1のB列から、「楽天」の文字列を検索するコードを作成します。

list_of_lists = worksheet1.col_values(2)
list_of_lists_in = [ s for s in list_of_lists if '楽天' in s]

【補足】col_volues(2)でB列を指定する

今回はB列を検索したいので、col_valuesの()の中に2を入れています。

A列を検索したい場合は、1が入ります。

【補足】list_of_listsの変数には、リストが入る

list_of_listリスト形式です。

list_of_lists_inの変数に、リストの中から、「楽天」の文字列を含むものだけを抽出していきます。

スプレッドシートの文字列を別シートに転記する方法

続いて、以下が文字列をsheet2に転記するためのコードです。

for i in list_of_lists_in:
    worksheet2.update_cell(1,1, i)

【補足】list_of_lists_in の変数もリスト形式

list_of_lists_inの中身をfor文で、取り出します。(結果的に、今回はB6セルの一つだけです)

【補足】シート2のA1セルを指定して、転記する

    worksheet2.update_cell(1,1, i)

list_of_lists_in の中身は一つだけなので、

(分かりやすく、)シート2の 【 1,1 セル=A1セル 】 を指定して、転記をします。

Pythonの実行結果

ここまでのコードをまとめたのが以下のスクリプトです。

実際にPythonを実行してみます。

import gspread
from oauth2client.service_account import ServiceAccountCredentials


scope = ["https://spreadsheets.google.com/feeds","https://www.googleapis.com/auth/spreadsheets","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json',scope)
client = gspread.authorize(creds)


#シートの選択
sh = client.open_by_key('xxxxxxxxxxxxxxxxxxxxxxxx')
worksheet1 = sh.sheet1
worksheet2 = sh.worksheet('sheet2')

list_of_lists = worksheet1.col_values(2)
list_of_lists_in = [ s for s in list_of_lists if '楽天' in s]

for i in list_of_lists_in:
    worksheet2.update_cell(1,1, i)

スプレッドシートのsheet2を確認してみます。

GAS GoogleSheetsAPI  スプレッドシート gmail_test1 sheet2「楽天」の文字列を検索して転記、実行結果
解決さん

やった!シート1のB6セルが、シート2に転記できた!

まとめ

この記事で学べること

  1. Google Sheets APIの使い方
  2. Pythonでスプレッドシートの文字列を検索する方法
  3. Pythonでスプレッドシートの文字列を別シートに転記する方法

今回は、Pythonとスプレッドシート連携の初歩として、

シート1から特定の文字列を検索して、そのセルをシート2へ転記する方法」をご紹介しました。

この課題を自作して、見様見真似で取り組んでみたのですが、初めてトライしたときは、

なんと、、、一ヶ月もかかってしまいました。

Pythonとスプレッドシートの連携をベースにして、

他にもさまざまなアプリケーションとの三重、四重の連携が考えられるので、

ぜひ、業務活用にも活かしてみてください。

【番外編】スプレッドシートの文字列を検索して、行単位で転記する方法

-Python日記
-