例えば画像のように、A1,C1,E1は上書きしたくないけど、その間のB1,D1,F1セルにsetValueしたいときに便利な方法をまとめました。
getRangeList
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Sheet1");
sheet.getRangeList(["B1","D1","F1"])setValue("hogehoge");
//B1,D1,F1セルそれぞれに"hogehoge"と出力される
getRangeListメソッドでは、すべてのセルに同じ値を入力することしかできません。
Sheets API
左側の「サービスを追加」より、Google Sheets APIを有効にする。
const resource = {
data : [
{
values : [null,100,null,200,null,300],
range : "sheet1!A1:F1"
}
] ,
valueInputOption : "USER_ENTERED",
};
Sheets.Spreadsheets.Values.batchUpdate(resource, "GSSID")
SheetsAPIでは、値にnullを指定することで、上書きしなくなります。
resource.dataに情報を追加することで、getRangeList()と同じような操作が可能です。また、範囲によってセットする値を変えられます。
SheetAPIのラッピング
いちいちこんな長ったらしい記述を書くのは面倒なので、ラッピングしてしまいます。
ソースコード
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;
}
}
使用方法
function test(){
const GSS = new GSSClass("SpreadSheet ID");
GSS.getRange(["シート1","A1:C1"],["シート1","A2:C2"]).setValues([[null,null,"b"]],[[1,1,1]]);
}