gas スプレッドーシートのセルの数式をgas内で計算したい

実現したいこと

スプレッドシートのセル内にある数式(例えば「-100-5」)をgasで取得し、gas内で指定した数値 + 取得した数式　の計算をgas内で実行したい。

発生している問題・分からないこと

どのようにすれば取得した文字列内の演算子を演算子として認識してくれるのか、また変数にあらかじめ代入した数式をgas内での別の場所で計算してくれるのかわからない。

該当のソースコード

GAS
1const ss = SpreadsheetApp.getActiveSpreadsheet();
2const sh = ss.getSheetByName("シート名");
3function o(){
4  let a = 150;
5  let b = sh.getRange('M8').getValue();　//数式(例:-100-5)が書いてあるセル
6  let c = a + b;   //理想はここで「150 - 100 - 5」の計算を実行して、cの返り値が45になって欲しい
7  return c;
8}

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

データ型をNumber()で数値に変換しようとすると演算子が文字列として認識されて「NaN」が返り、perseInt()で変換しようとすると演算子の手前だけを数値に変換されて「-100」が返ってどちらもうまくいきませんでした。

補足

特になし

YellowGreen

2024/04/17 19:32 編集

よくわかっていないかもしれませんが、作業用のセルを使って const ss = SpreadsheetApp.getActiveSpreadsheet(); const sh = ss.getSheetByName("シート名"); function o() { let a = 150; let b = sh.getRange('M8').getValue(); //数式(例:-100-5)が書いてあるセル // let c = a + b; //理想はここで「150 - 100 - 5」の計算を実行して、cの返り値が45になって欲しい let workCell = sh.getRange('A1') // 作業用セル workCell.setFormula('=' + a + b); let c = workCell.getValue(); workCell.clearContent(); return c; } のようなことはご希望とは違うのですね。

yamazaki_

2024/04/19 10:47

実際おそらくそれでできそうだということで、それに似た方法で作ってみてるのですが、もし処理がgas内で完結できるのであればスマートだし汎用性もあるからそんな方法がないだろうかと思って質問してみた次第です。

行動規範の内容に同意します

回答2件

「セルの数式」とありますが、M8セルに入っているのは -100-5ですか？それとも=-100-5ですか？
前者は文字列(text)で後者は数式(formula)です。

入っているのが数式であれば、該当のソースコード内Range.getValueの時点で計算結果の数値(-105)が取れるので期待した結果になるはずですが
そうでないということは文字列が入っているのでしょうか？

もし文字列の計算式であれば、基本的にjavascriptのNumberやparseInt関数では数式として計算できません。
eval(※危険な処理なので特にユーザの入力を直接evalしてはならない)か、自力で構文解析して四則演算とかになると思います。
GASはjavascriptベースなので、ググればjavascriptで文字列パースするやり方などは出てくるでしょう。

…というわけで、解決策としては

セルに文字列ではなく数式を入れる
どうしても必要なら自力で文字列パースする

になるのではないかと思います

投稿2024/04/17 12:25

pecmm

総合スコア406

yamazaki_

2024/04/17 13:54

数式ではなく文字列です。「a」の値にセルに入力した数式を加えて演算(例えばセルに「+23」と入力して「a+23」の計算)させたいので(つまり先頭が演算子から始まる)。今一応セルの文字列を分割して再結合して関数化してsetFormulaで一旦セルに記述して結果を取得し直す…みたいな方法でできないか試してみているのですが、forとかif文を色々使わないといけなくて大変なので、簡単かつ汎用性のある、変数内の数式を演算してくれる方法があれば知りたいなと思って質問してみました。ご提案いただいた方法調べて試してみます。ありがとうございます。

行動規範の内容に同意します

自己解決

実際はgas内で完結するようなものにしたかったのですが、なかなか難しそうだったので、スプレッドシートを介する方法でコードを書いてみました。引き続きわかる方は教えていただきたいです。

まず実際やりたいのは次のとおりです。

GAS
1let a = 3;
2let b = "+ 3 ) / 2";
3let d =  ( a + b;   //(3+3)/2のように、変数内の数式を別の数式内に組み込んで計算したい
4Logger.log(d);    //3

最終的に作ってみたコードが下のとおりです。(実際の使い方に当てはめて書いているので、本題と関係ない要素もありますがコードはこのままでも使えます。)
{前提: /< 切り上げ, /< 切り下げ, /_ 四捨五入}

GAS
1const ss = SpreadsheetApp.getActiveSpreadsheet();
2const sh4 = ss.getActiveSheet();
3function a(){
4  let result = 13
5  let calculation = "( 120 - ( number * 3 ) ) /< ( 2 + 2 ) - 3";  //実際は利用者にシートのセルに書き込んでもらう
6  let element = calculation.split(" ");
7  let verification = 0;
8  for(let i = 0; i <= element.length; i++){
9    if(element[i] == "number"){
10      element[i] = result;
11      verification += 1;
12    }
13  }
14  if(verification == 0){
15    let title = "抽出番号の挿入位置が未設定か、各演算子・数値間に半角スペースが入力されていない可能性があります。";
16    let message = "処理を中断し、数式を入力し直しますか?";
17    let warning = Browser.msgBox(title, message, Browser.Buttons.YES_NO);
18    if(warning == "YES"){
19     let exampleTitle = "抽出番号の挿入位置に「number」と記入し、負数の符号と数値以外の演算子・数値間に半角スペースを入力";
20     let example = "例: 15 + ( ( 15 * ( -13 + number ) ) / 2 - 3 ) ** 2";
21     Browser.msgBox(exampleTitle, example, Browser.Buttons.OK);
22     return
23    }
24  }
25  for(let i = 0; i <= element.length; i++){
26    if(element[i] == "/<"){
27      let {formulaArray, numFormula, roundNumber} = round(element, i);
28      let formula = "ROUNDUP(" + numFormula + ")";
29      formulaArray[roundNumber] = formula;
30      element = formulaArray;
31    }
32    if(element[i] == "/>"){
33      let {formulaArray, numFormula, roundNumber} = round(element, i);
34      let formula = "ROUNDDOWN(" + numFormula + ")";
35      formulaArray[roundNumber] = formula;
36      element = formulaArray;
37    }
38    if(element[i] == "/_"){
39      let {formulaArray, numFormula, roundNumber} = round(element, i);
40      let formula = "ROUND(" + numFormula + ")";
41      formulaArray[roundNumber] = formula;
42      element = formulaArray;
43    }
44  }
45  let formulaContent = element.join("");
46  Logger.log(formulaContent);
47  let sheetFormula = "=" + formulaContent;
48  Logger.log(sheetFormula);
49  sh4.getRange("V2").setFormula(sheetFormula);
50  let calculationResult = sh4.getRange("V2").getDisplayValue();
51  Logger.log(calculationResult);
52  return calculationResult;
53}
54
55function round(element ,i){
56  let formulaArray = element;
57  if(element[i-1] == ")"){
58    for(let j = 0; j <= i - 2; j--){
59      let parenthese = 1;
60      if(element[j] == ")"){
61        parenthese += 1; 
62      }
63      if(element[j] == "("){
64        parenthese -= 1;
65      }
66      if(parenthese == 0){
67        var frontStart = j;
68        var frontEnd = i - 1;
69        break
70      }
71    }
72  }
73  else{
74    var frontStart = i - 1;
75    var frontEnd = i - 1;
76  }
77  if(element[i+1] == "("){
78    for(let j = i + 2; j <= element.length; j++){
79      let parenthese = 1;
80      if(element[j] == "("){
81        parenthese += 1;
82      }
83      if(element[j] == ")"){
84        parenthese -= 1;
85      }
86      if(parenthese == 0){
87        var backStart = i + 1;
88        var backEnd = j;
89        break
90      }
91    }
92  }
93  else{
94    var backStart = i + 1;
95    var backEnd = i + 1;
96  }
97  let front = "";
98  for(let k = frontStart; k <= frontEnd; k++){
99    front += formulaArray[k];
100  }
101  formulaArray.splice(frontStart, frontEnd + 1);
102  let back = "";
103  for(let l = backStart - frontEnd - 1; l <= backEnd - frontEnd - 1; l++){
104    back += formulaArray[l];
105  }
106  formulaArray.splice(backStart - frontEnd - 1, backEnd - frontEnd - 1);
107  let roundNumber = backStart - frontEnd - 2;
108  let numFormula = front + "/" + back;
109  return {formulaArray, numFormula, roundNumber}
110}