はじめに

某web広告代理店で新卒としてエンジニアで働いている者です。最近ビジネスサイドや先輩からだんだんと信頼を掴んできたのか、「スプレッドシートで予定シートにURLを打ち込めば担当者シートから担当者を取得して予定シートに日付と担当者が自動入力されるようにしてほしい」と依頼をいただきました。

GASとは？

Google Apps Scriptの略で、JavaScriptベースで記述できるプログラミング言語です。Googleクラウド上で動くため、JSの書き方さえ分かれば、環境構築無しにエンジニアでなくても記述することができます。
このGASを使えば、スプレッドシートやドキュメント、GmailをはじめとするGoogleのアプリケーションの処理を自動化したり、連携することができます。

スプレッドシート構成

担当者シート

IDとURLとそのサイトの担当者が記載されています。
URLはドメイン単位で割り振られています。

予定シート

日付と案件名、URLと担当者が記載されています。
URLはドメイン以降も記述されており、ページごとにこのシートに記入します。
E列はURLをドメインまでで取得するためにarrayfomulaとsplitを使って/で分割しています。

GASの処理

GASを使う流れ

GASはスプレッドシートのツール→スクリプトエディタから記述することができます。

コード


function urlFix() {
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシート取得
  var objSheet = objSpreadsheet.getSheetByName('予定'); //シート名を元にシート取得
  var objCell = objSheet.getActiveCell(); //現在カーソルのあるセルを取得
  var row = objCell.getRow(); //セルの行数を取得
  var http = objSheet.getRange(row, 5).getValue(); //httpに当たる部分の取得
  var url = objSheet.getRange(row, 6).getValue(); //ドメインの部分を取得
  url = http + "//" + url + "/"; //担当者シートのURLと同じ形に整形
  var person = getPerson(url); //そのURLを投げて担当者を返してもらう
  if (objCell.getColumn() == 3) {
    objSheet.getRange(row, 4).setValue(person); //担当者をセット
    var today = new Date();
    var y = today.getFullYear();
    var m = today.getMonth() + 1;
    var d = today.getDate();
    objSheet.getRange(row,1).setValue(y + '/' + m + '/' + d); //日付をセット
  }
}

function getPerson(url) {
  var objSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = objSpreadsheet.getSheetByName('担当者');
  var personValues = sheet.getRange('C:C').getValues(); //担当者を全て配列で取得
  var urlArrays = sheet.getRange(1, 2, sheet.getLastRow()).getValues(); //URLを全て配列で取得
  urlArrays = Array.prototype.concat.apply([], urlArrays); //二次元配列を一次元に変換
  var personArrays = Array.prototype.concat.apply([], personValues); 
  var row = urlArrays.indexOf(url); //渡ってきたURLと同様のものが何行目にあるかを取得
  return personArrays[row];　//同様の行にある担当者取得

}

urlFix　urlを取得しgetPersonに渡す処理と返ってきた担当者、その日をスプレッドシートに挿入する処理
getPerson 渡ってきたURLを元に担当者を取得し返す処理
トリガーの設定で、スプレッドシートが編集時にurlFixが呼び出されるようにする。

できた処理

かなり荒いです

URLを入力した行に自動で日付と担当者が入ります。

難しかった点・感想

はじめはどこがアクティブなセルだとか、トリガーの編集時などのタイミングがわかりにくかった。
JSをしっかりと書いたことがなかったため、JSの勉強もしつつで進めたことにより理解に戸惑う点が多かった。
JSを描くという経験、Google Apps Scriptの利用が初めてだったため、何か間違っている、こここうして方がもっといいよね等ございましたらぜひご指摘ください！

　参考

Google Apps Script完全入門
＊JSの概念いついて半分くらいの量が割かれています。かなり初心者向けの本です。