SlackのSlash CommandsとGASを使って大量のデータが有るスプレッドシートの欲しい値を短時間で取ってくる方法

前提・実現したいこと

SlackのSlash CommandsとGASを使ってスプレッドシートの欲しい値を取ってくる

ここに質問の内容を詳しく書いてください。
該当ユーザの行を検索する際に、シートの後半のユーザーだと検索し終わるまでに時間がかかり、Slash Commands上でエラーメッセージとなってしまいます。

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

Slackbot  16:20
/**** はエラー「operation_timeout」により失敗しました

該当のソースコード

GoogleAppsScript
1function doPost(e) {
2  var verificationToken = e.parameter.token;
3  if (verificationToken != '****************') { // AppのVerification Tokenを入れる
4     throw new Error('Invalid token');
5  }
6
7  // Slackコマンドのパラメータ（該当ユーザのメールアカウント）取得
8  var useraccount = e.parameter.text;
9
10  // シート取得
11  var sheet = SpreadsheetApp.getActiveSheet();  
12
13  // 検索列指定（今回はC列）
14  var col = 'C';
15
16  // 該当ユーザの行を検索
17  for(var i=1;i<='100';i++){
18    if(sheet.getRange(col+i).getValue() === useraccount){
19
20      // 該当ユーザがある行を取得
21      var userrow = i;
22
23      // 該当ユーザの各サービスのアカウント権限値を取得
24      var valD = sheet.getRange('D'+userrow).getValue();
25      var valE = sheet.getRange('E'+userrow).getValue();
26      var valF = sheet.getRange('F'+userrow).getValue();
27      var valG = sheet.getRange('G'+userrow).getValue();
28
29      // 結果を取得
30      var kekka1 = useraccount+'が持っているアカウントは↓ '+'(★：オーナー  ◎：admin  ◯：user）'+' \n'+'Slack'+' : '+valD+' \n'+'GSuite'+' : '+valE+' \n'+'Docbase'+' : '+valF+' \n'+'Jira'+' : '+valG;
31
32      // 結果をSlackに返す
33      var response = {text: kekka1};
34      return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
35     }
36    }
37
38  //該当ユーザがいなかった場合の内容をSlackに返す
39  var kekka2 = useraccount+' のユーザーはいません'
40  var response = {text: kekka2};
41  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
42}
43

試したこと

考えていること

シートの値を１つ１つ検索しているため膨大な時間がかかっている。
なので、「シートの値を一気に取得してそこに該当のユーザーがいるかいないか？」のフローに書き換えた方がいい

困っていること

力不足でどう書き換えれば正しいかが分からないです...

補足情報（FW/ツールのバージョンなど）

これを参考に作っています。https://qiita.com/akibin/items/d7bda60b038890d770b7

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

回答1件

ベストアンサー

下記のように、最初に100セル分の値を一括して読み込んでおき、
その値を比較するのはどうでしょうか。

差分

diff
1  // 該当ユーザの行を検索
2-  for(var i=1;i<='100';i++){
3-    if(sheet.getRange(col+i).getValue() === useraccount){
4+  var data = sheet.getRange("C1:C100").getValues();
5+  for(var i=0; i<100; i++){
6+    if(data[i][0] === useraccount){
7
8      // 該当ユーザがある行を取得
9-      var userrow = i;
10+      var userrow = i+1;

　
全体

function doPost(e) {
  var verificationToken = e.parameter.token;
  if (verificationToken != '****************') { // AppのVerification Tokenを入れる
     throw new Error('Invalid token');
  }

  // Slackコマンドのパラメータ（該当ユーザのメールアカウント）取得
  var useraccount = e.parameter.text;

  // シート取得
  var sheet = SpreadsheetApp.getActiveSheet();  

  // 検索列指定（今回はC列）
  var col = 'C';

  // 該当ユーザの行を検索
  var data = sheet.getRange("C1:C100").getValues();
  for(var i=0; i<100; i++){
    if(data[i][0] === useraccount){
      // 該当ユーザがある行を取得
      var userrow = i+1;

      // 該当ユーザの各サービスのアカウント権限値を取得
      var valD = sheet.getRange('D'+userrow).getValue();
      var valE = sheet.getRange('E'+userrow).getValue();
      var valF = sheet.getRange('F'+userrow).getValue();
      var valG = sheet.getRange('G'+userrow).getValue();

      // 結果を取得
      var kekka1 = useraccount+'が持っているアカウントは↓ '+'(★：オーナー  ◎：admin  ◯：user）'+' \n'+'Slack'+' : '+valD+' \n'+'GSuite'+' : '+valE+' \n'+'Docbase'+' : '+valF+' \n'+'Jira'+' : '+valG;

      // 結果をSlackに返す
      var response = {text: kekka1};
      return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
     }
    }

  //該当ユーザがいなかった場合の内容をSlackに返す
  var kekka2 = useraccount+' のユーザーはいません'
  var response = {text: kekka2};
  return ContentService.createTextOutput(JSON.stringify(response)).setMimeType(ContentService.MimeType.JSON);
}

投稿2021/01/23 09:19