google-apps-script - 通过电报机器人和谷歌应用脚​​本在谷歌表格中填写订单

Question

我正在尝试通过电报机器人在谷歌表格中填写订单。在用户输入订单名称（例如“Sheet1”）后，我想一次从订单中发送一个项目的用户项目并填写谷歌表的相关答案。

例如：用户：Sheet1。机器人：苹果。用户：5。机器人：香蕉。用户：2

订单示例

现在我可以在一个答案中向用户发送所有项目，但我不知道如何一次发送一个项目并将答案写入相应的单元格。

var token = "xxx"; //token of telegram-bot from BotFather
var telegramUrl = "https://api.telegram.org/bot" + token;
var webAppUrl = "https://script.google.com/macros/s/xxx/exec"; 
var ssId = "xxx"; /*token of google spreadsheet in
https://docs.google.com/spreadsheets/d/xxx/edit#gid=0 */

function getMe() {
  var url = telegramUrl + "/getMe";
  var response = UrlFetchApp.fetch(url);}

function setWebhook() {
  var url = telegramUrl + "/setWebhook?url=" + webAppUrl;
  var response = UrlFetchApp.fetch(url);}

function sendText(id,text) {
  var url = telegramUrl + "/sendMessage?chat_id=" + id + "&text=" + text;
  var response = UrlFetchApp.fetch(url);}

function doGet(e) {
  return HtmlService.createHtmlOutput("Hi there");}

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var text = data.message.text;
  var id = data.message.chat.id;
  var name = data.message.chat.first_name;

  var out = [];  //array to store name of all sheets in spreadsheet
  var sheets = SpreadsheetApp.openById(ssId).getSheets(); 
  for (var y = 0 ; y < sheets.length ; y++) { 
  out.push( [ sheets[y].getName() ] );}

  var sheet = SpreadsheetApp.openById(ssId).getSheetByName(text);

  if (inArray(text, out) == true){
    var cache = CacheService.getScriptCache();
      if (cache.get('order') == text){
        var range_test = SpreadsheetApp.openById(ssId).getSheetByName(text).getRange("A1:A3").getValues();
        //B10 value is 0
        var range_item = SpreadsheetApp.openById(ssId).getSheetByName(text).getRange("B10").getValues();


        for (var r = 0; r < range_test.length; r++){       
          //send to telegram names of all items
          sendText(id,range_test[r])}


        //trying to cash some values and write it to a cell 
        var cashed_item = cache.put(range_item, range_test[0], 30);
        var cashed_item_get = cache.get(range_item)
        sheet.getRange('A9').setValue(text);
        sheet.getRange('A10').setValue(cashed_item_get);

        } 
      else {
            // Puts the value from text into the cache using the key 'order' 
            var cached = cache.put('order', text, 30)}
     } 
   else {sendText(id,"Error. Write the correct vendor name")
          sendText(id,out)}
}

function inArray(target, array) {
  for(var k = 0; k < array.length; k++)  {
    if(array[k] == target) {
      return true; }
     }
      return false; 
     }

现在上面的代码是这样工作的：PrtScn from telegram。用户两次输入供应商名称并获得所有项目名称的答案。

标签： google-apps-scriptgoogle-sheetstelegram-bot

Answer 1

 SpreadsheetApp.getActiveSpreadsheet().getSheetByName("666").getRange(57, 1).setValue([text])