MENU
＼ PayPayポイント最大23.5%！ ／
  1. ホーム
  2. デジタル
  3. プログラミング
  4. Googleスプレッドシートで銘柄コードを入力すると自動で同じ行に数式が入力されるGASを組みました。

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

デジタル プログラミング 株主優待

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

ミカログ
404: ページが見つかりませんでした | ミカログ

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

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

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

そこで、銘柄コードを入力しエンターキーを押すと、追加された行に応じて数式を自動入力する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列は２次元配列で書き込む

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

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

前半と同じです。

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

２次元配列の作成

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

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

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

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);

トリガーを設定

スクリプトを書いたら

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

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

サンプル

Google Docs
株主優待内容・権利確定月

参考サイト

teratail[テラテイル]
スプレッドシートに数式を自動で反映させたい。 Googleサイトのログを取得し、スプレッドシートへ反映して以下の様なリストを作成しております。 A・B列に、ログ履歴の日時とIDが取得された場合、E列とF列に記載されてい...
ひでぶろチャンネル
【Google Apps Script(GAS)】二次元配列の宣言や作成 以前の記事で、Google Apps Scriptにおける配列の重要性についてご紹介しましたが、今回は行と列を格納する事のできる二次元配列についてご紹介します。 ※以前の記事につい...

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

まとめ

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

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

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

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

デジタル プログラミング 株主優待
GAS Javascript スプレッドシート 株主優待
よかったらシェアしてね！
  • URLをコピーしました！
  • URLをコピーしました！
目次