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

2020年5月3日2022年7月16日

前回の記事で、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);

トリガーを設定

スクリプトを書いたら

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

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

サンプル