まえがき
GASを書くときに、SpreadSheetでシート「config」とかを作って、GAS内で使いたい文言やファイルIDなどをGAS外に溜める事が多いが、
その際に、GAS内でセル番地を指定して読み込ませる方法が一定してなくて面倒だったので、自分用にメモっておく
参考URL
基本ルールと例
シートの例
※送付状を差し込み印刷で作りたい、とかの場合に、例えばこんなシートを作成して、(内容はフィクションです
| シート「config」 | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | 年度 | 対象月 | 書類名 | 書類説明 | 送付状文言 | デフォルト送付部数 |
| 2 | 2021 | 1 | 見積書 | サービス申込時の見積書や、請求書発行時に申請される見積書 | 御見積書をお送りいたしますので、ご査収ください。 | |
| 3 | 2022 | 4 | 申込書 | サービス申込書 | 御申込書をお送りいたしますので、必要事項を御記入の上ご返送ください。 | 1 |
| 4 | 2023 | 7 | 契約書 | 相手が法人の場合、契約書になる | 御契約書をお送りいたしますので、必要事項を御記入の上、押印した1部をご返送ください。 | 2 |
| 5 | 2024 | 10 | 変更契約書 | サービス内容変更時とか | 変更契約書をお送りいたしますので、必要事項を御記入の上、押印した1部をご返送ください。 | 2 |
| 6 | 2025 | 請求書 | 定期の請求および、臨時で請求書を出す場合 | 御請求書をお送りいたしますので、期日までに記載の口座までお支払いください。 | ||
| 7 | その他 | 上記に当てはまらない書類 | 下記の書類をお送りいたしますので、ご査収ください。 |
コードの例
オブジェクトでセル番地を設定する。
config.gs
const RangeValue ={
config:{
fiscalYear:{row:2, column:1, height:5},
invoiceMonth:{row:2, column:2, height:4},
documentType:{row:2, column:3, height:6, width:3,innerColumn:{documentType:1, coverLetterText:2, defaultSendingNum:3}}
}
}
コードの説明(ルール)
RangeValue…セル番地を登録するオブジェクト。グローバルに置いて、セル番地は全部この中に収めておく。
シートごとに更にオブジェクト化する
セル番地の記載方法
<セル名>:{row:r, column:c, height:h, width:w}
rowとcolumn…シート内でのセル番地を入力(配列みたいに-1しないほうがいい
heightとwidth…セル範囲のときは、heightとwidthで範囲を定義する ※範囲の長さが不定のときはgetLastRowを使ったほうがいいかも
innerColumn:{a:1, b:2, c:3}
表のときは、innerRowとかinnnerColumnを作って、名前と順序を定義する
使い方サンプル
こんな感じで使う。
test.gs
const RangeValue ={
config:{
fiscalYear:{row:2, column:1, height:5},
invoiceMonth:{row:2, column:2, height:4},
documentType:{row:2, column:3, height:6, width:3,innerColumn:{documentType:1, coverLetterText:2, defaultSendingNum:3}}
}
main:{
documentType:{row:2, column:2}
}
}
const name_Sheet ={
config: "config",
main: "マスタ"
}
function test(){
const Ss = SpreadSheetApp.getActiveSpreadSheet();
const Sheet_config = Ss.getSheetByName(name_sheet.config);
const Sheet_main = Ss.getSheetByName(name_sheet.main);
//たとえばシート「マスタ」内の対象列にデータの入力規則を設定する
const array_documentType = Sheet_main.getRange(RangeValue.config.documentType.row,
RangeValue.config.documentType.column,
RangeValue.config.documentType.height).getValues().flat(); //シート「config」>書類名の列の値を配列として保存
let cellRule_documentType = SpreadSheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInList(array_documentType, true); //入力規則を作成
Sheet_main.getRange(RangeValue.main.documentType.row,
RangeValue.main.documentType.column,
Sheet_main.getLastRow() -RangeValue.main.documentType.row +1)
.setDataValidation(cellRule_documentType); //対象範囲に入力規則を設定
}
関数内にマジックナンバーがなくなって、グローバル側の定義(引いて言えばシート「config」)だけを弄るだけでよくなった。
その他
GAS×SpreadSheetって特にマジックナンバーが入りがちな言語なので、こういう方法でできるだけマジックナンバーを減らしていきたい。
slack連携とかの文言も、こういう風にSpreadSheet内に文言一覧を作れば定数文字列化を回避できそう。