GASでスプレッドシートの操作、別シート間のデータの繰り返し追加コピー処理

Question

前提・実現したいこと

SEOツールからCSVでエクスポートしたキーワード順位データを、
スプレッドシートに日毎で溜めていき、
データポータルのデータソースとして利用しようと考えています。

毎日の記録作業ですので、手順を簡略化したいと思い
GASでCSVデータを整形し日毎にワンボタンで記録する仕組みを作れないかと思い、
色々と調べて以下のコードを作成しました。

---

■スプレッドシートの構造
「入力」と「出力」のシートがあります。

➀「入力」シートはツールからエクスポートしたCSVを取り込むシートです。
※1行目は項目名
A,B,C,D列にそれぞれ
A2～Axxx：キーワード
B2～Bxxx：順位
C2～Cxxx：URL
D2：記録日
が入ります。

毎日、「入力」シートのデータをクリアします。
当日のデータをCSVからA～C(複数行あり)までコピペし、D2に記録日を入力します。

⓶「入力」シートから記録する
スクリプトを呼び出すボタンを押すと、本相談のコードが呼び出されます。

⓷「出力」シートには、
A列：キーワード
B列：日付
C列：順位
が「入力」に入った分だけ記録されます。
※日付けD2の1つしかデータがないので、繰り返し処理の中でコピーされる想定です。

翌日は、前日の記録の末尾の行から追加されていく想定です。

---

惜しいところまで書けたと思いましたが…
繰り返し処理内でエラーになってしまいます。

非エンジニアの為、JavaScriptもGASも基本的な部分が理解できていなかったら申し訳ありません。

助言いただけると嬉しいです。

Java Script
Google Apps Script
Google スプレッドシート

発生している問題・エラーメッセージ

Exception: 範囲の開始行の値が小さすぎます。

該当のソースコード

function getList() {
// 入力と出力のシートを取得
var ss_copyFrom = SpreadsheetApp.getActiveSpreadsheet();
var ss_copyTo = SpreadsheetApp.getActiveSpreadsheet();
var sheet_copyFrom = ss_copyFrom.getSheetByName('入力');
var sheet_copyTo = ss_copyTo.getSheetByName('出力');
 
var targetRowInput = sheet_copyFrom.getLastRow()+1;
var targetRowOutput = sheet_copyTo.getLastRow()+1;

// キーワードがあるだけ繰り返し転記  
var keyWord = sheet_copyFrom.getRange('A2'+':A'+targetRowInput).getValues();
var keyRank = sheet_copyFrom.getRange('B2'+':B'+targetRowInput).getValues();
var keyWordValue = {};
var keyRankValue = {};
var key = ""
var rank = 0
var copyDate = sheet_copyFrom.getRange('D2').getValues();
var count = keyWord.length;

  for(var i=0; i < count; i++)
  {
    keyWordValue[i] = keyWord[i];
    keyRankValue[i] = keyRank[i];
    key = keyWordValue[i];
    rank = keyRankValue[i];
    sheet_copyTo.getRange(i,1).setValue(key);
    sheet_copyTo.getRange(i,2).setValue(copyDate);
    sheet_copyTo.getRange(i,3).setValue(rank);
  }
}

試したこと

    sheet_copyTo.getRange(i,1).setValue(key);
    sheet_copyTo.getRange(i,2).setValue(copyDate);
    sheet_copyTo.getRange(i,3).setValue(rank);

↑この部分を

    sheet_copyTo.getRange(targetRowOutput+i,1).setValue(key);
    sheet_copyTo.getRange(targetRowOutput+i,2).setValue(copyDate);
    sheet_copyTo.getRange(targetRowOutput+i,3).setValue(rank);

↑このような書き方にすると、キーワードと順位は想定通りに記録されましたが、
B列の日付だけが、なぜかキーワードと順位より行数が5つほど多く記録されます。

おそらく、targetRowOutput+iの3連続で何か増えてしまっているんだと思うのですが…

Answer 1

ざっと見ただけですが、この部分は、getValues()ではなく、getValue()では？

var copyDate = sheet_copyFrom.getRange('D2').getValues();
　　　　　　　　　　　　　　↓
var copyDate = sheet_copyFrom.getRange('D2').getValue();

ここもgetRange()の行番号は１以降の番号を指定するので、iの範囲がおかしいです。

for(var i = 0; i < count; i++)
　　　　　　　↓
for(var i = 1; i <= count; i++)

---

(追記)
改めてコード読んでみると色々おかしいです。

var keyWord = sheet_copyFrom.getRange('A2'+':A'+targetRowInput).getValues();
var keyRank = sheet_copyFrom.getRange('B2'+':B'+targetRowInput).getValues();

上記コードは getValues() の結果なので keyWord も keyRank も2次元配列です。
1列だけなので、[["a"],["b"],["c"],...,["z"]]のような形になっているはず。

for(var i=0; i < count; i++)
{
    keyWordValue[i] = keyWord[i];
    keyRankValue[i] = keyRank[i];
    key = keyWordValue[i];
    rank = keyRankValue[i];
    //(以下省略)
}

keyWordValue[i] は i が1以降は undefined になっているはずです。

日付けの行だけなぜか6個ぐらい多く繰り返されてしまう

のもそれが原因でしょう。

以下修正してみたコードです。

for(let i = 0; i < count; i++)
{
    key =  keyWord[i][0]; //１列のみなので、2次元目のインデックスは常に 0
    rank = keyRank[i][0]; //同上
    sheet_copyTo.getRange(i + 2, 1).setValue(key); //ヘッダー行の１行を考慮して＋２
    sheet_copyTo.getRange(i + 2, 2).setValue(copyDate);
    sheet_copyTo.getRange(i + 2, 3).setValue(rank);}
}

Comments

[yuka0_o]

ご指摘ありがとうございます！

➀.getValues();　⇒　.getValue();
に修正しました。

②getRange()の行番号は１以降の番号を指定
に関してですが、

var targetRowInput = sheet_copyFrom.getLastRow()+1;
var targetRowOutput = sheet_copyTo.getLastRow()+1;

↑この部分で1行目を除外しているため、繰り返しでは0の方が都合がよく。。

､､､
sheet_copyTo.getRange(targetRowOutput+i,1).setValue(key);
sheet_copyTo.getRange(targetRowOutput+i,2).setValue(copyDate);
sheet_copyTo.getRange(targetRowOutput+i,3).setValue(rank);
､､､

今のところ、この状態だとキーワードと順位はイメージ通りに動いていますが、
日付けの行だけなぜか6個ぐらい多く繰り返されてしまうという所で躓いている状況です。

[yuka0_o]

ご回答ありがとうございました！

``` JavaScript
function getList() {
// 入力と出力のシートを取得
var ss_copyFrom = SpreadsheetApp.getActiveSpreadsheet();
var ss_copyTo = SpreadsheetApp.getActiveSpreadsheet();
var sheet_copyFrom = ss_copyFrom.getSheetByName('入力');
var sheet_copyTo = ss_copyTo.getSheetByName('出力');

var targetRowInput = sheet_copyFrom.getRange(1, 1).getNextDataCell(SpreadsheetApp.Direction.DOWN).getLastRow();

// キーワードがあるだけ繰り返し転記
var keyWord = sheet_copyFrom.getRange('A2'+':A'+targetRowInput).getValues();
var keyRank = sheet_copyFrom.getRange('B2'+':B'+targetRowInput).getValues();
var keyWordValue = {};
var keyRankValue = {};
var key = "";
var rank = 0;
var date = sheet_copyFrom.getRange('D2').getValue();
var count = keyWord.length;

for(var i = 0; i < count; i++)
{
key = keyWord[i][0];
rank = keyRank[i][0];
sheet_copyTo.getRange(i + 2 , 1).setValue(key);
sheet_copyTo.getRange(i + 2 , 2).setValue(date);
sheet_copyTo.getRange(i + 2 , 3).setValue(rank);
}
}
```

ご指摘の部分を見直しつつ、
targetRowInputの.getLastRow();を指定列の末尾を見るように修正したところ上手くいきました！
（※ここが意図する列ではなく、値が入っている別の列の末尾を見ていたため意図と違った繰り返し回数だった）

ありがとうございました。