Google App Scriptを使ってみよう Part 5
今回からは、ある依頼の製作をしていきます。
プロジェクトでは、apple、banana、cherryの3つのチームが構成され、1年以上が経過し、合計20人のメンバーが在籍しています。メンバー情報は「agents」というシートに保存されています。最近、マネージャーから「自分のチームのメンバーだけを閲覧したい」という依頼がありました。これに応えるためにGASを使用して、メンバー情報を加工する必要があります。
今あるデータをどのように加工して依頼に答えていくかがテーマになります。
大まかな処理工程
(1) メンバー情報の取得
(2) 配属先の特定
(3) メンバー情報をチームシートに表示
工程単位で、説明していきます。
最終的には、distributeMembersという関数の中に全工程で書いたコードを入れて完成させます。
(1) メンバー情報の取得
まず、agentsシートのメンバー情報が表示されている複数行データを取得してください。
コードを書いたら保存して、実行し意図したようにデータが取れているか確認しましょう。
ヒント：agentsシートの複数行データをmembersという箱に格納してください。
以前、やったことのあるコーディングでできますよ！
function getMembersInAgentsSheet( ){
const members = ‘ここにコードを書いてください’
console.log(members)
}
答え⇩
function getMembersInAgentsSheet( ){
const members = SpreadsheetApp.openById(‘スプレッドシートID’).getSheetByName(‘agents’).getDataRange( ).getValues( ).slice(1)
console.log(members)
}
上記でslice(1)というコードが出てきましたが、これはスプレッドシートの１行目を取り除いてくれます。
例：slice(3)とした場合は、最初の3行を取り除いた残りのデータを扱うことができます。
(2) 配属先の特定
(1)で取得したメンバー情報を、チームごとにフィルタリングしていきます。
イメージ：フィルタリングする際は、複数行データに対してfilterを使用します。
イメージとして[ [ ‘apple’, ‘agent’ ], [ ‘banana’, ‘agent’ ], [ ‘cherry’, ‘agent’ ] ]という複数行データの中から、appleチームに所属しているメンバーだけをフィルタリングすると[ [ ‘apple’, ‘agent’ ] ]を取得できます。
上記をコードで表現すると…
function filterMemebersByAppleTeam(){
const members = [ [ ‘apple’, ‘agent’ ], [ ‘banana’, ‘agent’ ], [ ‘cherry’, ‘agent’ ] ]
const membersOfAppleTeam = members.filter( record => record[0] === ‘apple’ )
console.log(membersOfAppleTeam)
}
record => record[0] === ‘apple’
(recordは、各自自由に命名できます。)
recordは、複数行データの各行データを指します。
record[0]は、行データの中に入っている0番目の値を指します。
===は、===の直前に指定したものと直後に指定したものが同じかどうかの条件式
※行データの中に2つの値が入っている場合、1つ目の値を表現する場合は、行データ[0]と表します。
目で見た順番に1を引けば良いと覚えることも可能です。
members.filter( record => record[0] === ‘apple’ )は、各行データの0番目の値がappleの行データのみ抽出してください。
filterの動きをAさんとBさんの対話形式で言語化してみると…
Aさん: 最初の行データを見てください。
Bさん: わかりました。[ ‘apple’, ‘agent’ ]ですね。
Aさん: 行データの0番目の値は何が入っているかわかりますか？
Bさん: えーと、appleという文字が入っていますね。
Aさん: 了解です。実は、appleという文字が入っていたら、その行データを取得したかったので、そのままにしておいてください。
Bさん: わかりました。
Aさん: 次に、[ ‘banana’, ‘agent’ ]を見てください、0番目の値はappleという文字ですか。
Bさん: いいえ、違います。0番目の値はbananaという文字でした。
Aさん: そうでしたか、appleではないので、消していただいて問題ないです。
Bさん: 了解しました。
Aさん: 最後の、[ ‘cherry’, ‘agent’ ] はどうですか。
Bさん: cherryという文字が入っていたので、appleではないです。これも消しますか。
Aさん: はい、お願いします。
Bさん: 承知です。結果的に[ [ ‘apple’, ‘agent’ ], [ ‘banana’, ‘agent’ ], [ ‘cherry’, ‘agent’ ] ]の中で、0番目がappleなのは[ [ ‘apple’, ‘agent’ ] ]でした。
Aさん: お疲れ様です。ありがとうございました。
シートmembersからメンバー情報を取得して、各チームごとにフィルタリングするコーディングをしましょう。
できたら、保存して、実行して、意図したようにデータを取れているか確認しましょう。
function filterMembersByEachTeam( ){
const members = ‘コードを書いてください’
const membersOfAppleTeam = ‘コードを書いてください’
const membersOfBananaTeam = ‘コードを書いてください’
const membersOfCherryTeam = ‘コードを書いてください’
console.log(membersOfAppleTeam)
console.log(membersOfBananaTeam)
console.log(membersOfCherryTeam)
}
答え⇩
function filterMembersByEachTeam( ){
const members = [ [ ‘apple’, ‘agent’ ], [ ‘banana’, ‘agent’ ], [ ‘cherry’, ‘agent’ ] ]
const membersOfAppleTeam = members.filter( record => record[0] === ‘apple’ )
const membersOfBananaTeam = members.filter( record => record[0] === ‘banana’)
const membersOfCherryTeam = members.filter( record => record[0] === ‘cherry’)
console.log(membersOfAppleTeam)
console.log(membersOfBananaTeam)
console.log(membersOfCherryTeam)
}
(3) メンバー情報をチームシートに表示
最後は、チームごとにフィルタリングした複数行データをスプレッドシートの各シートに表示させます。
まずは、下記の問題にトライしてみましょう！
agentsシートからメンバー情報を取得して、下記のmembersという箱に格納し、各チームごとにフィルタリングするコーディングをしましょう。
できたら、保存して、実行して、意図したようにデータを取れているか確認しましょう。
function filterMembersByEachTeam( ){
const members = ‘シートagentsのメンバー情報を取得するコードを書いてください’
const membersOfAppleTeam = ‘membersの箱の中身の中でappleチームに配属されている行データのみにするコードを書いてください’
const membersOfBananaTeam = ‘membersの箱の中身の中でbananaチームに配属されている行データのみにするコードを書いてください’
const membersOfCherryTeam = ‘membersの箱の中身の中でcherryチームに配属されている行データのみにするコードを書いてください’
console.log(membersOfAppleTeam)
console.log(membersOfBananaTeam)
console.log(membersOfCherryTeam)
}
答え⇩
function filterMembersByEachTeam( ){
const members = SpreadsheetApp.openById(‘スプレッドシートID’).getSheetByName(‘agents’).getRange(‘A2:D21’).getValues( ).slice(1)
const membersOfAppleTeam = members.filter(r => r[0] ===’apple’)
const membersOfBananaTeam = members.filter(r => r[0] ===’banana’)
const membersOfCherryTeam = members.filter(r => r[0] ===’cherry’)
console.log(membersOfAppleTeam)
console.log(membersOfBananaTeam)
console.log(membersOfCherryTeam)
}
上記のコードに⇩下記のコードを追加してください。
そうすることでAppleチームのメンバー情報がappleシートに表示されます。
SpreadsheetApp.openById(‘スプレッドシートID’).getSheetByName(‘apple’).getRange( 2, 1, membersOfAppleTeam.length,membersOfAppleTeam[0].length).setValues(membersOfAppleTeam)
length は配列や文字列の「長さ」や「要素の数」を知るために使います。
トライしよう！
(3) appleメンバー情報をチームシートに表示できたら、banana、cherryにもメンバー情報が表示されるようにコードを書き換えてみましょう！
練習問題
下記のコードを書き換えてみよう！
各行データの２個目の値がagentになっているものをフィルタリングしてください。
ヒント：下記のコードの２箇所を変えると答えがでてきます。
function filterMemebersByAppleTeam( ){
const members = [ [ ‘apple’, ‘agent’ ], [ ‘banana’, ‘agent’ ], [ ‘cherry’, ‘agent’ ] ]
const membersOfAppleTeam = members.filter( record => record[0] === ‘apple’ )
console.log(membersOfAppleTeam)
}
答え⇩
function filterMemebersByAppleTeam(){
const members = [ [ ‘apple’, ‘agent’ ], [ ‘banana’, ‘agent’ ], [ ‘cherry’, ‘agent’ ] ]
const membersOfAppleTeam = members.filter( record => record[1] === ‘agent‘ )
console.log(membersOfAppleTeam)
}
弊社では、Google Apps Script を使用した、業務効率化、自動化を行う部門があります。Google Work Spaceをご利用の企業様、個人事業主様、自動化などでお困りでしたら、弊社までお問い合わせください。
何百の自動化プログラムを組んできたエンジニアが、お客様の業務自動化をご支援いたします！