LoginSignup
@takuya134 (takuya aaaa)

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

googleスプレッドシートでデータの入力規則を用いた繰り返しのマクロを組みたい

Q&A

Closed

spreadsheetマクロデータの入力規則

googleスプレッドシートでデータの入力規則で条件式付きのプルダウンをマクロを使い量産したいと考えています。
したいことは、

1)D2セルに[データの入力規則]にて['参照データ1月～3月販売情報\'!B2:CW2]参照のプルダウンを作成。
2)D3セルに[データの入力規則]にて['参照データ1月～3月販売情報\'!B3:CW3]参照のプルダウンを作成。
～省略～
3)D999セルに[データの入力規則]にて['参照データ1月～3月販売情報\'!B999:Z999]参照のプルダウンを作成。

となります。

/** @OnlyCurrentDoc */

function myFunction() {
  for(let i=2;i<=999;i++){
        var spreadsheet = SpreadsheetApp.getActive();
        spreadsheet.getRange('D(i)').activate();
        spreadsheet.getCurrentCell().setValue('\'参照データ_1月～3月_販売情報\'!B(i):CW(i)');
  }
};

試しにD2セルを手動で実施したマクロを上記のように書き換え実行したら下記の画像のようにエラーとなりました。
マクロ初心者なので何が原因かわかりませんでした。ご教授願えますでしょうか。

2021-12-21 05.00.40 script.google.com a11239de2157.png

0 likes

1Answer

tanaike
@tanaike

修正点

  • D(i)はA1Notationではないためエラーが発生してしまいます。
  • ループ内でsetValueを使用すると、スクリプトのコストが高くなります。
  • Data validationsはsetValueではなく、setDataValidationで使用します。

これらの修正点をスクリプトに反映しますと、次のように修正できます。

修正したスクリプト

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1"); // ここにドロップダウンリストを挿入したいシートのシート名を入れて下さい。
  var dataValidations = [];
  for (let i = 2; i <= 999; i++) {
    dataValidations.push([SpreadsheetApp.newDataValidation().requireValueInRange(ss.getRange(`'参照データ_1月～3月_販売情報'!B${i}:CW${i}`)).build()]);
  }
  sheet.getRange(2, 4, dataValidations.length).setDataValidations(dataValidations);
}

注意点

2 - 999へData validationsを設定しようとしますと、上記のスクリプトでも実行時間は長くなると思います。そのため、最初は例えばスクリプトの動作テストとして、999を10などと小さくしてテストを行うと見やすいかと思います。これで問題がないようであれば999に戻して実行してみてください。それでも実行時間が長いようですと、Sheets APIを使用することも検討してみてください。

0Like

Your answer might help someone💌

LoginSign Up