GoogleAppsScript，ボタンを押したときのみ，任意の列の計算を更新

Question

追記内容

下記参照
qnoir様，ご指摘ありがとうございます．

現状

現コードでは添付の画像結果が出力されます．

目的，ゴール

スクリプトによって
「ボタンを押したときのみ，任意の列の計算を更新する」
「C列配置のチェックボックスをTrueにし，かつ，値の貼り付けで完了，かつすでに貼り付けが完了している場合は更新しない」
を達成したいです．
このようにすることで，GoogleSheets再計算のたびにAPIリソースが消費されてしまうことを回避する
ことが目的です

エラー概要

図の例では，( K, 9 ), ( K, 10 )が翻訳されるはず，と期待しています．
１，
K列に計算結果を出力させたく，
"range"定義に合わせて
関数により（無理やり）範囲指定したものを実行させていますが，
エラーが発生しました．
GoogleTranslate関数の第1引数が範囲指定だから，ということが想定されます．
２，
なお，arrayformula関数で配列式も試しましたが，
GoogleTranslate関数は複数の対象を一つのセル内に出力するため，断念しました．

現コード

// ボタンで指定セルに指定計算結果を, https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q11157169156
function click() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var range = sh.getRange('K2:K')
  range.setFormula('=IF(C2:C=True, GOOGLETRANSLATE(H2:H, "en", "ja"), "")');
  range.setValue(range.getValue())
}

お聞きしたいこと

1, 回答者様が考えてらっしゃるベストプラクティス
2, 計算式だけの解決であれば計算式の修正
3, 計算式ではない点で修正が必要であれば

ご教授いただけたら幸いです．
ご返信お待ちしております．よろしくお願いいたします．

すでに頂いているご質問について

① 質問のコードで何かエラーが発生しているのでしょうか？
GoogleTranslate関数の第1引数が範囲指定だから，ということが想定されます．

②もしくは、 質問のコードでは、希望する動作にならないのでしょうか？
はい，希望する動作になっておりません．関数計算の結果が出力されることを希望しています．

１．今回制御対象とするのは、Ｋ列の2行目以降のセル（行数は不定）である。 ＞＞＞ おっしゃるとおりでございます．
２．Ｃ列に手入力でデータが入っており、Ｋ列の各行は、Ｃ列の数値を使って計算した計算結果を表示する。 ＞＞＞ おっしゃるとおりでございます．
３．ボタンを押したら、下記の動作を行うものとする。
＿・「Ｃ列に値が入っているが、Ｋ列には値の入っていない"行"」に対して
＿＿　=IF(C▲=True, 1, "")　※▲は各行番号
＿＿　に該当する数式を設定する。（Ｋ列のうち、すでに値貼り付けとなっているセルには数式を設定しない） ＞＞＞ おっしゃるとおりでございます．

４．各行に数式を設定した直後、数式設定したセルに対して、その計算結果を値貼り付けする。 ＞＞＞はい，計算結果が関数のままでないことを達成したいです．

Comments

[退会済みユーザー]

① 質問のコードで何かエラーが発生しているのでしょうか？
→その場合は、エラーの内容を具体的に書いてください。

②もしくは、 質問のコードでは、希望する動作にならないのでしょうか？
→であるならば、希望する動作を「具体的に」書いてください。
現状の文とコードからは、希望されている具体的な動作がいまいちわかりません。

特に「任意の列」という表現や「値の貼り付けで完了，かつ次回以降は値の貼り付けされた行に関しては更新しない」の部分。これだけでは具体的な動作がはっきりわかりません。

コードからエスパーを発動させて読み解くに、たとえば、下記のような理解でよろしいでしょうか？

１．今回制御対象とするのは、Ｋ列の2行目以降のセル（行数は不定）である。
２．Ｃ列に手入力でデータが入っており、Ｋ列の各行は、Ｃ列の数値を使って計算した計算結果を表示する。
３．ボタンを押したら、下記の動作を行うものとする。
＿・「Ｃ列に値が入っているが、Ｋ列には値の入っていない"行"」に対して
＿＿　=IF(C▲=True, 1, "")　※▲は各行番号
＿＿　に該当する数式を設定する。（Ｋ列のうち、すでに値貼り付けとなっているセルには数式を設定しない）

４．各行に数式を設定した直後、数式設定したセルに対して、その計算結果を値貼り付けする。

[Por]

① 質問のコードで何かエラーが発生しているのでしょうか？
GoogleTranslate関数の第1引数が範囲指定だから，ということが想定されます．

②もしくは、 質問のコードでは、希望する動作にならないのでしょうか？
はい，希望する動作になっておりません．関数計算の結果が出力されることを希望しています．

１．今回制御対象とするのは、Ｋ列の2行目以降のセル（行数は不定）である。 ＞＞＞ おっしゃるとおりでございます．
２．Ｃ列に手入力でデータが入っており、Ｋ列の各行は、Ｃ列の数値を使って計算した計算結果を表示する。 ＞＞＞ おっしゃるとおりでございます．
３．ボタンを押したら、下記の動作を行うものとする。
＿・「Ｃ列に値が入っているが、Ｋ列には値の入っていない"行"」に対して
＿＿　=IF(C▲=True, 1, "")　※▲は各行番号
＿＿　に該当する数式を設定する。（Ｋ列のうち、すでに値貼り付けとなっているセルには数式を設定しない） ＞＞＞ おっしゃるとおりでございます．

４．各行に数式を設定した直後、数式設定したセルに対して、その計算結果を値貼り付けする。 ＞＞＞はい，計算結果が関数のままでないことを達成したいです．

ご指摘ありがとうございます．
引き続きよろしくお願いいたします．

[退会済みユーザー]

（最初の投稿時には画像は貼り付けられていませんでした）
その状態で、当初の質問文から私の最初にイメージしていたものは下記のようなものです。
・ボタンが1個ある。
・そのボタンを1回押すと、Ｋ列の2行目以降の対象となる全行に対して、一括して更新処理及び値貼付処理を行う。

そうではなくて正確には、
・1個のボタンと、C列の各行にチェックボックスがある。
・チェックを入れてボタンを押したら、「C列チェックボックスにチェックが入っている行（のＫ列のセル）」のみ、数式を新規設定（すでに値貼付け済みとなっているセルは数式を再設定）し、計算後値貼り付けする。
・一番最初はＫ列のどのセルにも数式が入っていなくて構わない

このような理解で合っていますか？

[Por]

そうではなくて正確には、
・1個のボタンと、C列の各行にチェックボックスがある。
・チェックを入れてボタンを押したら、「C列チェックボックスにチェックが入っている行（のＫ列のセル）」のみ、数式を新規設定（すでに値貼付け済みとなっているセルは数式を再設定）し、計算後値貼り付けする。
・一番最初はＫ列のどのセルにも数式が入っていなくて構わない

こちらがおっしゃるとおりでございます（数式は固定の予定です）．
汲み取っていただきありがとうございます．

Answer 1

御記載の通り、GOOGLETRANSLATE関数は、先頭行だけH2:Hのような書き方をしても複数行に対して適用できないようです。

下記コードでは、C列にチェックの入っている行について、K列に数式を設定し、直後に値貼り付けします。

（なお、2行目以降の対象行のA列～Ｋ列の中で列結合されている行が存在する場合、下記２つのコードは正しく動作しないことがあります）

function myFunction1() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();
  const lastRow = sh.getRange('C:C').getLastRow();

  for (let i = 2; i <= lastRow; i++) {
    // C列の値を取得
    const check = sh.getRange('C' + i).getValue();
    // C列のうちチェックが入っている行の処理
    if (check === true) {
      // K列を数式に置き換える。
      sh.getRange('K' + i).setFormula('=GOOGLETRANSLATE(H' + i + ', "en", "ja")');
      // K列を更新（値貼付）
      sh.getRange('K' + i).setValue(sh.getRange('K' + i).getValue());
    }
  }
}

上のコードでは1行ずつ読み取って1行ずつ書き込んでいます。行数によっては、下のように一括して書き込む方が若干早いかもしれません。（なお、下のコードはチェックが入っていない行に数式が入っている場合も全部値貼り付けになります）

function myFunction2() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getActiveSheet();
  const range = sh.getRange('K2:K')

  // C列の値を取得
  const checks = sh.getRange('C2:C').getValues().flat();

  // C列のうちチェックが入っている行のインデックスを取得
  const checkedIndices = checks.map((e, i) => e ? i : false).filter(i => i !== false);

  // K列の現状の値を取得
  const originalValues = range.getValues().flat();

  // K列のうち、C列にチェックが入っている行を数式に置き換える。チェックが入っていない行は元の値のまま。
  const modifiedValues = originalValues.map((e, i) =>
    checkedIndices.includes(i) ? [`=GOOGLETRANSLATE(H${i + 2}, "en", "ja")`] : [e]);

  // K列を更新（数式含む）
  range.setValues(modifiedValues);

  // K列を更新（全部値貼付）
  range.setValues(range.getValues());
}

Comments

[Por]

無事稼働いたしました．大変ありがとうございます．チェックを条件にして
１，For文で1行ずつ数式計算させる
２，インデックス取得させて数式計算させる
大変勉強になりました．

[Por]

一点ご質問よろしいでしょうか．

私の方で確認が漏れておりましたが，「C列配置のチェックボックスをTrueにし，かつ，値の貼り付けで完了，かつすでに貼り付けが完了している場合は更新しない」の
完了している場合は更新しない（スキップする），の部分について
ご教授いただくことは可能でしょうか．

もしお気づきの際，お時間いただける際で結構です．
いつでもご連絡お待ちしております．

[Por]

※追記
私の方で試行錯誤してみました．
結果，現在未だうまくいっておりません．

修正点は，貼り付け列"F列"の値取得，値が空白である条件で分岐，頂いた関数の稼働，という内容になります．

```
function click(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sh = ss.getActiveSheet();
const lastRow = sh.getRange('B:B').getLastRow();

for (let i = 2; i <= lastRow; i++) {
const check = sh.getRange('B' + i).getValue();
const translated = sh.getRange('F:F');
if (translated.isBlank()){
if (check === true ){
sh.getRange('F' + i ).setFormula('=googletranslate(E' + i + ', "en", "ja")');
sh.getRange('F' + i ).setValue(sh.getRange('F' + i).getValue());
}
}
}
}
```

結果，関数が稼働しておりません．
もしお気づきの際，お時間いただける際で結構です．
いつでもご連絡お待ちしております．

[退会済みユーザー]

const translated = sh.getRange('F:F')
これを
const translated = sh.getRange('F' + i)
に修正してみて下さい。