setValuesで飛び地のセルも一括で更新する方法

例えば画像のように、A1,C1,E1は上書きしたくないけど、その間のB1,D1,F1セルにsetValueしたいときに便利な方法をまとめました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2953722/9867d501-1718-7a9c-70e0-b5556e5dacc1.png)

<h1>getRangeList</h1>

```js
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Sheet1");
sheet.getRangeList(["B1","D1","F1"])setValue("hogehoge");
//B1,D1,F1セルそれぞれに"hogehoge"と出力される
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2953722/69d7e8e7-cbf9-273a-a1ff-af91274f9236.png)

:::note warn
getRangeListメソッドでは、すべてのセルに同じ値を入力することしかできません。
:::
<h1>Sheets API</h1>

左側の「サービスを追加」より、Google Sheets APIを有効にする。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2953722/433e3352-8eae-1fb6-89d0-f5156e91b4ce.png)


```js
const resource = {

      data :   [
            {
              values : [null,100,null,200,null,300],
              range : "sheet1!A1:F1"
            }
          ] ,

      valueInputOption : "USER_ENTERED",

    };

Sheets.Spreadsheets.Values.batchUpdate(resource, "GSSID")


```

:::note 
SheetsAPIでは、値にnullを指定することで、上書きしなくなります。
:::
:::note
resource.dataに情報を追加することで、getRangeList()と同じような操作が可能です。また、範囲によってセットする値を変えられます。
:::

<h1>SheetAPIのラッピング</h1>

いちいちこんな長ったらしい記述を書くのは面倒なので、ラッピングしてしまいます。

<h3>ソースコード</h3>

```js

class GSSClass{

  constructor ( gssId ) {

    this.gssId = gssId;

    return this;

  }

  /**
   * @param requiredRange {array} 範囲の指定
   * 1. [["シート名","A1"]]
   * 2. [["シート名",1,1]]
   * 3. [["シート名","A1:A5"]]
   * 4. [["シート名",1,1,5,1]]
   * 5. [["シート名","A1"],["シート名","B3"]]
   * @return range Range 
   */
  getRange( ...requiredRange ){

    if(Array.isArray(requiredRange)){

      const rangeArray = [] ;


      for (let i=0,l = requiredRange.length ; i<l;i++){

        switch ( requiredRange[i].length ) {

          case(2):

            rangeArray.push(

              `${requiredRange[i][0]}!${requiredRange[i][1]}`

            );
            break;
          
          case(3):

            rangeArray.push(

              `${requiredRange[i][0]}!${GSSUtils.toStringColIndex(requiredRange[i][2])}${requiredRange[i][1]}`

            )
            break;

          case(4):

          rangeArray.push(

            `${requiredRange[i][0]}!${GSSUtils.toStringColIndex(requiredRange[i][2])}${requiredRange[i][1]}:${GSSUtils.toStringColIndex(requiredRange[i][2])}${requiredRange[i][3] + requiredRange[i][1]-1}`

          )
          
          break;            

          case(5):

          rangeArray.push(

            `${requiredRange[i][0]}!${GSSUtils.toStringColIndex(requiredRange[i][2])}${requiredRange[i][1]}:${GSSUtils.toStringColIndex(requiredRange[i][4] + requiredRange[i][2]-1)}${requiredRange[i][3] + requiredRange[i][1]-1}`

          )
          
          break;

        }

      }

    return new RangeClass(rangeArray,this.gssId);

    }else{

      console.log(typeof requiredRange,requiredRange)

      const errorMessage = "範囲の指定方法が異なります。";

      throw new Error(errorMessage);

    }
  }

}

class RangeClass extends GSSClass{

  constructor ( range , gssId ) {

    super(gssId);

    this.range = range;

    return this;

  }
  /**
   * @return values {object} {range : values}
   */

  getValues(){

    const options = {

      ranges : this.range,

      majorDimension: "ROWS"

    }

    const values = Sheets.Spreadsheets
      　　　　　　　 　　.Values
      　　　　　　　　　 .batchGet(this.gssId, options)
      　　　　　　　　　 .valueRanges;

    const object = {};

    for (let i=0,l=values.length;i<l;i++) {

      const range = values[i]["range"].replace(/'/g, "");

   　　object[range] = values[i]["values"];
   　}

    return object;    

  }

  setValues(...arr){

    const resource = {

      data : [],

      valueInputOption : "USER_ENTERED",

    };

    for (let i=0,l=arr.length;i<l;i++){

      resource.data.push({

        values : arr[i],

        range : this.range[i]

      })

    }
    
    Sheets.Spreadsheets.Values.batchUpdate(resource, this.gssId)

  }



}

class GSSUtils {
     
   static toNumericColIndex ( string_col_index ) {

      const s = string_col_index.toUpperCase();

      let n = 0;

      for (let i=0,l=s.length; i<l; i++) {

        n = (n * RADIX) + (s.charCodeAt(i) - A + 1);

      }

      return n;

    }

  static toStringColIndex ( numeric_col_index ) {
      let n = numeric_col_index;
      let  s = "";
      while (n >= 1) {

        n--;

        s = String.fromCharCode(A + (n % RADIX)) + s;

        n = Math.floor(n / RADIX);
        
      }
      return s;
    }

  }

```



<h3>使用方法</h3>

```js
function test(){
  const GSS = new GSSClass("SpreadSheet ID");
  GSS.getRange(["シート1","A1:C1"],["シート1","A2:C2"]).setValues([[null,null,"b"]],[[1,1,1]]);
}
```