Help us understand the problem. What is going on with this article?

GASでパターン網羅を作る

More than 1 year has passed since last update.

Google Apps Scriptを利用してスプレッドシートに改行付きでまとめられた表を分解して組み合わせパターン網羅表を作ることになったときのためのTips。

今回のゴール

シート1で一行ごとにCaseAとCaseBの改行をそれぞれ配列にしてAとBの組み合わせ網羅表をシート2に作る。
スクリーンショット 2018-09-18 2.29.50.png

スクリプトエディタを開く

「ツール」→「スクリプトエディタ」で開けます

実際のコード

function myFunction() {
  var bk = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = bk.getSheetByName("シート1");
  var sheet2 = bk.getSheetByName("シート2");
  var data = sheet1.getRange("A:A").getValues();
  var counter = 0;

  for(var i =1;i<data.length;i++){
    if(sheet1.getRange(i+1, 1).getValue() != ""){
      var a_arr = getCaseA(sheet1,i);
      var b_arr = getCaseB(sheet1,i);
      for(ai in a_arr){
        for(bi in b_arr){
          counter++;
          sheet2.getRange(counter+1,1).setValue(counter);
          sheet2.getRange(counter+1,2).setValue("【CaseA】"+a_arr[ai]+ String.fromCharCode(10) +"【CaseB】"+b_arr[bi]);
        }
      }
    }    
  }
}

function getCaseA(sheet,target){
  return sheet.getRange(target+1,2).getValue().split(/\r\n|\r|\n/);
}

function getCaseB(sheet,target){
  return sheet.getRange(target+1,3).getValue().split(/\r\n|\r|\n/);
}

実行は再生ボタンを押せば動きます。

シート2の結果

期待したとおりの結果が表示されました。
スクリーンショット 2018-09-18 2.38.39.png

getValues使えばもっと簡略化できそうな気もしなくもないですが。。。
それはまた今度時間ができたときゆっくり見ます。

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away