E
E
easydroop2020-05-12 18:24:57
Bots
easydroop, 2020-05-12 18:24:57

How to format script results for output?

In my Google spreadsheet, using third-party services, information is collected on the costs of an advertising campaign and on profits, and the share of advertising costs is calculated. The whole thing is updated every hour. In order not to enter the table every time, I decided to make a bot that, upon request, would give me this data in Telegram. I found an article on the Internet on how data from Google spreadsheets is dragged to Telegram, and substituted my values:

function doPost(e) {
  // получаем сигнал от бота
  var update = JSON.parse(e.postData.contents);
  
  // проверяем тип полученного, нам нужен только тип "сообщение"
  if (update.hasOwnProperty('message')) {
    var msg = update.message;
    var chatId = msg.chat.id;
    
    // проверяем, является ли сообщение командой к боту
    if (msg.hasOwnProperty('entities') && msg.entities[0].type == 'bot_command') {
      
      // проверяем на название команды - /stat
      if (msg.text == '/stat') {
        
        // если все проверки пройдены - запускаем код, который ниже, 
        // открываем оглавление нашего канала 
        var sheet =  SpreadsheetApp.openById('1auEoZHq5--BRF6MKrghzexbsQzbD0R5WnUR3B9Ukj9A').getSheets()[1]
        
        // достает последний пост
        var drr = sheet.getRange(sheet.getLastRow(), 1, 1,  3).getValues()[0]
        var message = '<strong>Ежедневный отчет</strong>\n' + 'Потрачено с НДС: ' + drr[0] + '₽' + '\n' + 'Доход: ' + drr[1]  + '₽' + '\n' +
          'ДРР: ' + drr[2]*100+'%'        
        //формируем с ним сообщение
        var payload = {
          'method': 'sendMessage',
          'chat_id': String(chatId),
          'text': message,
          'parse_mode': 'HTML'
        }     
        var data = {
          "method": "post",
          "payload": payload
        }
        
        // и отправляем его боту (замените API на свой)
        var API_TOKEN = '000000:ABCDZ'
        UrlFetchApp.fetch('https://api.telegram.org/bot' + API_TOKEN + '/', data);
      }
    }
  }
}


Everything worked out, except that the data is not rounded:

Daily report
Spent with VAT: 54335.42399999999
Income: 577447
DRR: 9.409594993133567%


What parameter should be added to variables so that they round up? I rummaged through the documentation, but apparently due to the fact that I have no experience in programming, I did not find anything.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2020-05-13
@easydroop

I recommend using the methodgetDisplayValues()

var drr = sheet.getRange(sheet.getLastRow(), 1, 1, 3).getDisplayValues()[0];

then. you don't have to worry about data types in the code, and it will be enough to cast the data type in the cell. If it is visible in the cell 0.00%, it will return exactly 0.00%if 1-02-20, then 1-02-20.
This is the smallest and most reliable solution in this case.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question