Googleスプレッドシートで銘柄コードを入力すると自動で同じ行に数式が入力されるGASを組みました。

スポンサーリンク

前回の記事で、ImportXML関数を使い、銘柄コードを入力することで株主優待の情報等を表示するスプレッドシートを紹介しました。

このスプレッドシートは自分でも便利に使っているのですが、

数式をいちいちコピーするの、めんどくさいな・・・

と感じるようになりました。

そこで、銘柄コードを入力しエンターキーを押すと、追加された行に応じて数式を自動入力するGAS(Google Apps Script)を組みました。

こんな動きになります

A列に銘柄コードを入力し確定→変更がトリガーとなってGASスクリプトが動き、B列、D〜H列に関数を自動で入力します。

スポンサーリンク

コード

function myFunction() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("株主優待内容");
  
  var ary = []
  var lastRow,strformula
  
  lastRow = sheet.getLastRow()
  for (i=2; i<=lastRow; i++)  {
    strformula = "=ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/?scode=\",A"+i+"),\"//*[@id=\'CONTENTS_MAIN\']/div[3]/div/div/h1\")"
    ary.push([strformula])
  }
    var rows = ary.length;
    sheet.getRange(2,2,rows,1).setValues(ary);
    

    
    var sheet = spreadsheet.getSheetByName("株主優待内容");
    var ary = []
    var lastRow,strformula,strformulakeihi,strformulaname,strformulanaiyou,strformulahp,strformuladay,strformulaprice,i
    
    lastRow = sheet.getLastRow()
    
  for (i=2; i<=lastRow; i++)  {
   strformulakeihi = "=H"+i+"*C"+i
    strformuladay = "=ImportXML(CONCATENATE(\"https://kabuoji3.com/stockholder/\",A"+i+",\"/\"),\"//*[@id=\'base_box\']/div/div[3]/dl[2]/dd\")"
    strformulanaiyou="=CONCATENATE(ImportXML(CONCATENATE(\"https://kabuoji3.com/stockholder/\",A"+i+",\"/\"),\"//*[@id=\'base_box\']/div/div[5]/h3\"))"
    strformulahp="=CONCATENATE(\"https://kabuoji3.com/stockholder/\",A"+i+",\"/\")"
    strformulaprice="=LEFT(CONCATENATE(ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/yutai/?scode=\",A"+i+",),\"//*[@id=\'CONTENTS_MAIN\']/div[4]/dl[1]/dd\")),LEN(CONCATENATE(ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/yutai/?scode=\",A"+i+",),\"//*[@id=\'CONTENTS_MAIN\']/div[4]/dl[1]/dd\")))-1)"
    ary.push([strformulakeihi,strformuladay,strformulanaiyou,strformulahp,strformulaprice])
  }
    
    var rows = ary.length;
    var cols = ary[0].length;
    sheet.getRange(2,4,rows,cols).setValues(ary);
    
}

各々の説明です。

スプレッドシートの指定

「株主優待内容」シートを指定します。

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName("株主優待内容");

空の配列を作成

var ary = []

最後の行を取得

getLasRow()で最後の行を取得します。
strformulaは銘柄コードから銘柄名を取得する関数ですが、合わせて宣言しています。

var lastRow,strformula  
lastRow = sheet.getLastRow()

数式を配列に格納

数式をセルに一つずつ書き出すのではなく、まず数式を一旦配列に格納します。

配列に格納した後に一気にセルに書き出すことでスプレッドシートの呼び出し回数を減らすことができ、時間の短縮になります。

数式
=ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/?scode=\",A"+i+"),\"//*[@id=\'CONTENTS_MAIN\']/div[3]/div/div/h1\")

セルが可変のため行番号はA+iとし、for文で行番号の部分を変えています。

for (i=2; i<=lastRow; i++)  {
    strformula = "=ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/?scode=\",A"+i+"),\"//*[@id=\'CONTENTS_MAIN\']/div[3]/div/div/h1\")"
    ary.push([strformula])
  }

ary.push([strformula])で配列を追加していきます。

lastRowが13なら、

[“=ImportXML…A2…”][“=ImportXML…A3…”]….[“=ImportXML…A13”]の配列が作られます。

配列をB列2行目から書き出し

getRange(行番号、列番号、行数、列数)で範囲を指定し、setValuesで書き出します。

var rows = ary.length;
sheet.getRange(2,2,rows,1).setValues(ary);

これで、B列への書き込みができました!

D〜H列は2次元配列で書き込む

D〜H列もほぼ同じですが、B列は1次元の配列だったのに対しD〜H列は、「D2セル〜H列の最後の行まで」と2次元の配列を作ってから範囲内に書き出しています。
B列と同じように1列ごとに書き込んでも良いのですが、書き込み回数をなるべく減らした方が処理が早いので。

シートの選択、空の配列の作成、宣言、最終行の取得

前半と同じです。

 var sheet = spreadsheet.getSheetByName("株主優待内容");
    var ary = []
    var lastRow,strformula,strformulakeihi,strformulaname,strformulanaiyou,strformulahp,strformuladay,strformulaprice,i
    
    lastRow = sheet.getLastRow()

2次元配列の作成

D〜H列に入力する2次元配列を作ります。

配列[strformulakeihi][strformuladay][strformulanaiyou][strformulahp][strformulahp]

をfor文で回し、2次元配列を作っています。

for (i=2; i<=lastRow; i++)  {
   strformulakeihi = "=H"+i+"*C"+i
    strformuladay = "=ImportXML(CONCATENATE(\"https://kabuoji3.com/stockholder/\",A"+i+",\"/\"),\"//*[@id=\'base_box\']/div/div[3]/dl[2]/dd\")"
    strformulanaiyou="=CONCATENATE(ImportXML(CONCATENATE(\"https://kabuoji3.com/stockholder/\",A"+i+",\"/\"),\"//*[@id=\'base_box\']/div/div[5]/h3\"))"
    strformulahp="=CONCATENATE(\"https://kabuoji3.com/stockholder/\",A"+i+",\"/\")"
    strformulaprice="=LEFT(CONCATENATE(ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/yutai/?scode=\",A"+i+",),\"//*[@id=\'CONTENTS_MAIN\']/div[4]/dl[1]/dd\")),LEN(CONCATENATE(ImportXML(CONCATENATE(\"https://www.nikkei.com/nkd/company/yutai/?scode=\",A"+i+",),\"//*[@id=\'CONTENTS_MAIN\']/div[4]/dl[1]/dd\")))-1)"
    ary.push([strformulakeihi,strformuladay,strformulanaiyou,strformulahp,strformulaprice])
  }

書き出し

書き出します。

var rows = ary.length;
var cols = ary[0].length;
sheet.getRange(2,4,rows,cols).setValues(ary);

トリガーを設定

スクリプトを書いたら

編集→現在のプロジェクトのトリガーを選択し、マクロを実行するトリガーを「編集時」に設定します。

これで、どこかのセルが変更された時にスクリプトが動くようになります!

サンプル

株主優待内容・権利確定月
株主優待内容 銘柄コード,銘柄名,株数,必要経費,権利確定日,優待内容,優待内容詳細,最新株価 2702,日本マクドナルドホールディングス,100,¥530,000,12月末日・6月末日,優待食事券, 3191,ジョイフル本田,200,¥264,000,6月20日,①商品券(500円券) または ②茨城県産米,

参考サイト

スプレッドシートに数式を自動で反映させたい。|teratail
Googleサイトのログを取得し、スプレッドシートへ反映して以下の様なリストを作成しております。 A・B列に、ログ履歴の日時とIDが取得された場合、E列とF列に記載されている数式を、自動で反映させたいのですが、可能でしょうか? E列とF列には、以下の様な数式が記載されています。 E列:=V
【Google Apps Script(GAS)】二次元配列の宣言や作成
以前の記事で、Google Apps Scriptにおける配列の重要性についてご紹介しましたが、今回は行と列を格納する事のできる二次元配列についてご紹介します。 ※以前の記事についてはこちらをご覧ください。

↑サンプル3の書き方を採用させてもらってます。

まとめ

A列にコードが入力されると、他のセルに自動で数式が入力されるスプレッドシートを作りました!

プログラマではないので、書き方がスタイリッシュでない点はお許しください。

ARRAYFORMULAという数式が使えれば早そうだったのですが、複雑な数式のせいか全くうまくいきませんでした。

もしARRAYFORMULAでのやり方をご存知であればコメントに残してもらえると嬉しいです。