Google Apps Scriptを使用して、指定したセルに関数を入力し計算後に値貼りしたい

Question

実現したいこと

特定のステータスの場合にGASを使用し関数を入力したいですが
"" や ' などを併用しているためエラーが発生し対応できず困っているのと
ステータス確認のために変数をどう指定すればいいかご教授いただきたいです。

また関数のセル指定で i を使用し適切な行数になるようにしたいです。
そして最後に値貼りにし実行完了とできるでしょうか。

発生している問題・エラーメッセージ

構文エラー: SyntaxError: missing ) after argument list

該当のソースコード

function input() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName("シート名");  // シートの取得
  const lastRow = sheet.getLastRow();
  
  for(let i = 2; i <= lastRow; i++) {

      // ステータスの確認・関数の入力
      if (Status == "更新可") {
      //↑で特定のステータスのみ処理にしたいが、変数をどう入力すればよいか分からず困っています
      //ステータスはA列です

        if(!sheet.getRange(i, 5).getValue()){
          sheet.getRange(i, 5).setValue('入力したい関数');//E列に指定した関数を入力する
        } else {
        // デバッグ用
        console.log(sheet.getRange(i, 1).getValue());//処理対象外のステータス確認
      }
    }
  }
  //最後に関数で計算した後、値貼りにしたいがどのように記述すればいいでしょうか
}

下記の関数で●にしている部分で i を使用し適切な行数にすることはできるでしょうか。
また実現したい事にも記載しましたが、"" や ' などを併用しているためエラーが発生してしまいますが解消できるでしょうか。

=SUM(QUERY(IMPORTRANGE("ブックID","シート名!$A:$G"),"select Col5 where Col2='"&B●&"' and Col1 = date '"& TEXT(C●,"YYYY-MM-DD") &"'",0))

Answer 1

sheet.getRange(i, 5).setValue('入力したい関数');//E列に指定した関数を入力する

こちらに関しては、setFormula()　を使用すると実現できるかと思います。

使用例などはこちらを参照してください

使用コードの抜粋

//この関数を実行する
function sample() {
  var sheet = SpreadsheetApp.getActiveSheet(); //　現在のシートを取得
  var d1 = sheet.getRange('D1'); //　使っていないセルを取得
  d1.setFormula('=SUM(A2, B2)'); //　関数を設定して演算
  var functionResult = d1.getValue();　// 演算結果を取り出し
  var c2 = sheet.getRange('C2'); //　合計値を格納するセルを取得
  c2.setValue(functionResult); // 合計値を格納
  d1.clear() // 演算で利用したしたセルを初期状態に戻す
}

=SUM(QUERY(IMPORTRANGE("ブックID","シート名!$A:$G"),"select Col5 where Col2='"&B●&"' and Col1 = date '"& TEXT(C●,"YYYY-MM-DD") &"'",0))

GASのスクリプトで文字列変数として定義し、iを埋め込む
こんな感じのコードではないでしょうか？

function test() {
  let i = 1
  // `` で囲み、変数を埋め込む場合は ${変数名} とする
  let str = `=SUM(QUERY(IMPORTRANGE("ブックID","シート名!$A:$G"),"select Col5 where Col2='"&B${i}&"' and Col1 = date '"& TEXT(C${i},"YYYY-MM-DD") &"'",0))`
  console.log(str)
}

consoleの結果
=SUM(QUERY(IMPORTRANGE("ブックID","シート名!$A:$G"),"select Col5 where Col2='"&B1&"' and Col1 = date '"& TEXT(C1,"YYYY-MM-DD") &"'",0))

---

二つを合わせてこのようなイメージではないかと思いますが、いかがでしょうか？

let i = 1
let str = `=SUM(QUERY(IMPORTRANGE("ブックID","シート名!$A:$G"),"select Col5 where Col2='"&B${i}&"' and Col1 = date '"& TEXT(C${i},"YYYY-MM-DD") &"'",0))`
sheet.getRange(i, 5).setFormula(str)

---

追加の回答

変数の宣言が　const Status = sheet.getRange(i, 1).getValue　だと実行できずに困っています。

恐らく、getValue　の後に () がないため、実行できないのではないかと思います。

const Status = sheet.getRange(i, 1).getValue()

あとは、forループの i が2から始まっているので
使用するスプレッドシートの2行1列のところに値が入力されているかも、確認してみてください。

・最後に関数で計算した後、値貼りにしたい

2つ目は値のみを上書きしたいので、後者となります。

関数入力セルに関数をセット後、値を取得し
関数ではなく、値をセットすることで実現できるのではないかとみてます。

let i = 2
let formulaStr = `関数` 

// 関数入力セルに関数をセット
sheet.getRange(i, 5).setFormula( formulaStr )

// 関数入力セルから値を取得
let val = sheet.getRange(i, 5).getValue()

// 関数入力セルに値を設定
sheet.getRange(i, 5).setValue(val)

Comments

[teratail1234]

ご回答ありがとうございます。
問題なく対応でき大変助かりました。

また質問の記載が分かりづらいようで申し訳ないですが、あと2点ございます。

・特定のステータスの場合に関数を入力したい（ステータスはA列です）
if (Status == "更新可") で判定したいのですが
変数の宣言が　const Status = sheet.getRange(i, 1).getValue　だと実行できずに困っています。

・最後に関数で計算した後、値貼りにしたい
こちらは実現できますでしょうか。

[k.a_teratail]

1つ目のご質問、回答の方に追記しました。

2つ目のご質問について

> ・最後に関数で計算した後、値貼りにしたい
関数入力セルの結果（値のみ）を別のセルに貼り付けたいということでしょうか？
※関数入力セルの 入力欄には「=関数()」、別のセルの 入力欄には「値」が表示される

それとも
関数入力セルに値のみを上書きする感じでしょうか？
※関数入力セルの 入力欄には「=関数()」 ではなく、「値」が表示される

[teratail1234]

初歩的な事ですみません。ありがとうございます。
問題なく対応できました。

2つ目は値のみを上書きしたいので、後者となります。

[k.a_teratail]

値上書きとのことで、回答に追記しました。
ご確認の方、よろしくお願いします。

[teratail1234]

全て対応できました。
細かく記載していただき本当にありがとうございます。