Last active
January 10, 2023 11:01
-
-
Save maximlunegov/86f8848b0c6b14ca1c1a4fa78e365595 to your computer and use it in GitHub Desktop.
Берёт покупателей и продавцов из списка
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"timeZone": "Asia/Yekaterinburg", | |
"dependencies": { | |
"libraries": [ | |
{ | |
"userSymbol": "BetterLog", | |
"libraryId": "1DSyxam1ceq72bMHsE6aOVeOl94X78WCwiYPytKi7chlg4x5GqiNXSw0l", | |
"version": "27" | |
} | |
] | |
}, | |
"webapp": { | |
"executeAs": "USER_DEPLOYING", | |
"access": "ANYONE_ANONYMOUS" | |
}, | |
"exceptionLogging": "STACKDRIVER", | |
"runtimeVersion": "V8", | |
"sheets": { | |
"macros": [ | |
{ | |
"menuName": "Новый макрос", | |
"functionName": "myFunction" | |
} | |
] | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// How to connect your Telegram Bot to a Google Spreadsheet (Google Apps Script) | |
// https://www.youtube.com/watch?v=mKSXd_od4Lg | |
// | |
// This code must be added to the Google Apps Script file attached to the spreadsheet script editor. | |
// Full steps in the readme | |
const ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var ssId = ss.getId(); | |
const sheet = ss.getSheetByName('Settings'); | |
const token = sheet.getRange('A2').getValue(); | |
const telegramApi = "https://api.telegram.org/bot" + token; | |
const parse_mode = "html" //если ставить Markdown, то не работают обратные уведомления о получении сообщения (не может распарсить отдельные знаки) | |
const webAppUrl = sheet.getRange('C2').getValue(); | |
const adminID = sheet.getRange('D2').getValue(); | |
function getMe() { | |
if (sheet == null) { | |
Browser.msgBox('Please DO NOT delete or rename the Settings sheet. Read "Description and manual" on README sheet'); | |
return; | |
} | |
const url = telegramApi + "/getMe"; | |
const response = UrlFetchApp.fetch(url); | |
Logger.log(response.getContentText()); | |
} | |
function setWebhook() { | |
const url = telegramApi + "/setWebhook?url=" + webAppUrl; | |
const response = UrlFetchApp.fetch(url); | |
Logger.log(response.getContentText()); | |
} | |
function sendText(id,text) { | |
options = {muteHttpExceptions: true}; | |
const url = telegramApi + "/sendMessage?chat_id=" + id + "&text=" + encodeURIComponent(text) + "&parse_mode=" + parse_mode; | |
const response = UrlFetchApp.fetch(url, options); | |
Logger.log(response.getContentText()); | |
} | |
function doGet(e) { | |
return HtmlService.createHtmlOutput("Здравствуйте!"); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* // ответ на команду /pokupka | |
if(text == '/pokupka') | |
{ | |
const choices = ['Склад сырья','Склад готовой продукции']; | |
const text = 'Выберите склад'; | |
sendText(id,agents_hello); // отправляет в бот | |
sendKeyboard(id, choices, text); | |
} | |
// ответ на команду Приход | |
if(text == 'Приход') | |
{ | |
const bazaDan = ss.getSheetByName('Справочник'); | |
const choices = flatten(bazaDan.getRange("Справочник!F5:F").getValues().filter(String)); | |
const text = 'Выберите продавца'; | |
sendKeyboard(id, choices, text); | |
} | |
// ответ на команду Расход | |
if(text == 'Расход') | |
{ | |
const bazaDan = ss.getSheetByName('Справочник'); | |
const choices = flatten(bazaDan.getRange("Справочник!G5:G").getValues().filter(String)); | |
const text = 'Выберите покупателя'; | |
sendKeyboard(id, choices, text); | |
} */ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Logger = BetterLog.useSpreadsheet(ssId); | |
function debug(data) { | |
SpreadsheetApp.openById(ssId).getSheetByName("Log").getRange("A2"). | |
setValue(data); //Вторая ячейка на листе Log, для отладки | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// за основу взято по видео, код отсюда https://github.com/meneer-code/Connect-Telegram-Bot-to-Google-Sheets/blob/master/code.gs | |
function doPost(e) { | |
// this is where telegram works | |
try { | |
const data = JSON.parse(e.postData.contents); | |
const text = data.message.text; | |
var id = data.message.chat.id; | |
var new_row = []; | |
const name = data.message.chat.first_name /* + " " + data.message.chat.last_name; */ | |
//это кусок кода для простого принятия сообщений | |
// const valuesToSheet = text.split(" "); // разбить сообщение по словам */ | |
/* let Svodki = SpreadsheetApp.openById(ssId).getSheets()[0]; // выбрать первый лист */ | |
// const answer = name + ", ваше сообщение " + text + " получено"; */ | |
// sendText(id,answer); // отправить сообщение о получении текста | |
/* GmailApp.sendEmail(Session.getEffectiveUser().getEmail(),"Ботом получено сообщение",JSON.stringify(data,null,4)) //отправка сообщение на почту */ | |
//SpreadsheetApp.openById(ssId).getSheets()[0].appendRow([new Date(),id,name,text,answer]); //добавляем сообщения на лист как есть | |
// SpreadsheetApp.openById(ssId).getSheets()[0].appendRow([new Date().toLocaleDateString("ru-RU"),id,name].concat(valuesToSheet)); //добавляем сообщения с разбивкой по столбцам | |
// добавляем данные на конкретный лист через @ | |
/* if(/^@/.test(text)) { | |
const sheetName = text.slice(1).split(" ")[0]; //разделяет полученный текст по пробелу, начиная со второго символа (1), т.е. пропустив @, а потом берёт первую половину [0], как имя листа | |
// в следующей переменной до знака вопроса пытается открыть лист по имени (условие), если открывается, то открывается (значение1), если нет, тогда создаёт его (значение2) | |
const sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName) ? SpreadsheetApp.openById(ssId).getSheetByName(sheetName) : SpreadsheetApp.openById(ssId).insertSheet(sheetName); | |
const NewValuesToSheet = text.split(" ").slice(1); // разделяет слова по пробелу после отбрасывания @ | |
sheet.appendRow([new Date().toLocaleDateString("ru-RU")].concat(NewValuesToSheet)); | |
sendText(id,"Ваш текст '" + NewValuesToSheet + "' добавлен на лист '" + sheetName + "'"); // отправляет в бот | |
//const newText = text.split(" ").slice(1).join(" "); объединяет текст после отбрасывания @ | |
//sheet.appendRow([new Date(),id,name,newText]); | |
//sendText(id,"Ваш текст '" + newText + "' добавлен на лист '" + sheetName + "'"); // отправляет в бот | |
} */ | |
// ответ на команду /start обычный | |
if(text == '/start') | |
{ | |
const greeting = "Добро пожаловать в бот склада производства \n <b>ООО \"SNK-Allhim\"</b>" | |
const choices = ['Приход','расход']; | |
const text = 'Выберите операцию'; | |
sendText(id,greeting); // отправляет в бот | |
sendKeyboard(id, choices, text); | |
} | |
// КОМАНДЫ ПО КНОПКАМ | |
// ответ на команду /pokupka | |
if(text == '/pokupka') | |
{ | |
const choices = ['Приход','Расход']; | |
const text = 'Выберите операцию'; | |
sendKeyboard(id, choices, text); | |
} | |
// ответ на команду Приход | |
if(text == 'Приход') | |
{ | |
const bazaDan = ss.getSheetByName('Справочник'); | |
const choices = flatten(bazaDan.getRange("F5:F").getValues().filter(String)); | |
const text = 'Выберите продавца'; | |
const sheetName = "Склад_сырья"; | |
const sheet = SpreadsheetApp.openById(ssId).getSheetByName(sheetName) ; | |
const newText = "Приход"; | |
sendKeyboard(id, choices, text); | |
sheet.appendRow([new Date().toLocaleDateString("ru-RU"),newText]); | |
} | |
// ответ на команду Расход | |
if(text == 'Расход') | |
{ | |
const bazaDan = ss.getSheetByName('Справочник'); | |
const choices = flatten(bazaDan.getRange("G5:G").getValues().filter(String)); | |
const text = 'Выберите покупателя'; | |
sendKeyboard(id, choices, text); | |
new_row.push(text); | |
} | |
if (new_row.length === 1) | |
{sendText(id,new_row)} | |
} | |
catch(e) { | |
sendText(adminID, JSON.stringify(e,null,4)); | |
} | |
debug(data) | |
} | |
function testKeyboard() { | |
const Id = 2094253; // REPLACE THIS | |
const bazaDan = ss.getSheetByName('Справочник'); | |
/* const choices = bazaDan.getRange("Справочник!D5:D").getValues().filter(String); */ | |
const choices = flatten(bazaDan.getRange("D5:D").getValues().filter(String)); | |
const text = 'Выбор поставщика'; | |
console.log(choices); | |
sendKeyboard(Id, choices, text); | |
Logger.log(choices); | |
} | |
//код выводит значение последней ячейки в нужном столбце | |
function test() { | |
const sh = ss.getSheetByName('Справочник'); | |
const lrow = sh.getLastRow(); | |
const Avals = sh.getRange("D1:D"+lrow).getValues(); | |
const Alast = lrow - Avals.reverse().findIndex(c=>c[0]!=''); | |
const Alastvalue = sh.getRange(`D${Alast}`).getValues(); | |
sendText(Id, Alastvalue); | |
Logger.log(Alastvalue); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!DOCTYPE html> | |
<html> | |
<head> | |
<base target="_top"> | |
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script> | |
</head> | |
<body> | |
<script> | |
google.script.host.close(); | |
</script> | |
</body> | |
</html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function gif(text){ | |
var gifUrl = 'https://cdn-images-1.medium.com/max/1600/1*9EBHIOzhE1XfMYoKz1JcsQ.gif'; | |
if (!text) text = ' '; | |
var gifTemplate = '<img src="' + gifUrl + '" alt="Progress Indicator" style="width: 100%;height: auto; ">'; | |
var html = HtmlService.createHtmlOutput(gifTemplate).setHeight(225).setWidth(300); | |
SpreadsheetApp.getUi().showModalDialog(html, text); | |
}; | |
function alert_dialog(text){ | |
SpreadsheetApp.getUi().alert(text); | |
}; | |
function close_form(text){ | |
if (!text) text = ' '; | |
var html = HtmlService.createTemplateFromFile('formReadyClose.html').evaluate().setHeight(20).setWidth(50); | |
SpreadsheetApp.getUi().showModalDialog(html, text); | |
}; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// функция клавиатуры взята отсюда https://stackoverflow.com/questions/48431352/telegram-bot-keyboard-with-google-apps-script/72147367#72147367 | |
function sendKeyboard(chatId, choices, text) { | |
const buttons = transformArrayToKeyboard(choices); | |
const replyKeyboardMarkup = {keyboard: buttons, | |
one_time_keyboard: true, | |
resize_keyboard: true}; | |
const replyMarkup = JSON.stringify(replyKeyboardMarkup); | |
const url = telegramApi + '/sendMessage?chat_id=' + encodeURIComponent(chatId) + '&text=' + encodeURIComponent(text) + '&disable_web_page_preview=true&reply_markup=' + encodeURIComponent | |
(replyMarkup); | |
const response = UrlFetchApp.fetch(url); | |
Logger.log(response.getContentText()); | |
} | |
function sendMessage(from_chat_id, text, keyboard, id_msg) { // Отправляет сообщение используя sendMessage | |
var data = { | |
method: "sendMessage", | |
chat_id: String(from_chat_id), | |
text: text, | |
parse_mode: "HTML", | |
reply_markup: JSON.stringify(keyboard), | |
reply_to_message_id: String(id_msg) | |
}; | |
var options = { | |
method: 'POST', | |
payload: data, | |
muteHttpExceptions: true | |
}; | |
var response = UrlFetchApp.fetch('https://api.telegram.org/bot' + token + '/sendMessage', options); | |
console.log(JSON.parse(response.getContentText(), null, 7)) | |
} | |
let keyboard_contact = { | |
keyboard: [ | |
[{ | |
text: "Авторизоваться", | |
request_contact: true | |
}] | |
], | |
resize_keyboard: true, | |
one_time_keyboard: true | |
}; | |
// функция разделяет список слов на команды меню, по три кнопки в ряду (с правильными скобками) | |
function transformArrayToKeyboard(choices) { | |
const maxLengthRowKeyboardMenu = 3; | |
const arr = choices.map(item => ({text: item })); | |
const result = []; | |
let index = 0; | |
while(arr.slice(index).length > 0) { | |
const newRowValuesForButtons = arr.slice(index,index+maxLengthRowKeyboardMenu); | |
result.push(newRowValuesForButtons); | |
index += maxLengthRowKeyboardMenu; | |
} | |
return result; | |
} | |
// функция собирает содержимое ячеек в массив https://medium.com/@mars_escobin/creating-your-own-trivia-bot-on-telegram-d6cc9cddcc7f | |
function flatten(arrayOfArrays) { | |
return [].concat.apply([],arrayOfArrays); | |
} | |
// ещё по кнопке https://qna.habr.com/q/650074 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
function main(){ | |
gif("Проводим операции..."); | |
var ss = SpreadsheetApp.getActive(); | |
var from_sheet = ss.getSheetByName("Склад_готовой_продукции"); | |
var to_sheet = ss.getSheetByName("Склад_сырья"); | |
var data = from_sheet.getRange(5, 1, from_sheet.getLastRow() - 4, 11).getValues(); | |
var spec_sheet = ss.getSheetByName("Спецификации"); | |
var spec_data = spec_sheet.getRange(5, 1, spec_sheet.getLastRow() - 4, 5).getValues(); | |
var av_sheet = ss.getSheetByName("Аналитика"); | |
var av_data = av_sheet.getRange(5, 1, av_sheet.getLastRow() - 4, 3).getValues(); | |
var consumes = {}; | |
for (var i = 0; i < data.length; i++){ | |
if (!data[i][1]) | |
break; | |
if (data[i][10]) | |
continue; | |
if (data[i][1] != "Создание готовой продукции") | |
continue; | |
var average_price = 0; | |
for (var j = 0; j < spec_data.length; j++){ | |
if (!spec_data[j][0]) | |
break; | |
if (spec_data[j][0] != data[i][2]) | |
continue; | |
if (!consumes[spec_data[j][2]]) | |
consumes[spec_data[j][2]] = 0; | |
consumes[spec_data[j][2]] = consumes[spec_data[j][2]] + (spec_data[j][4] * data[i][4]); | |
for (var k = 0; k < av_data.length; k++){ | |
if (!av_data[k][0]) | |
break; | |
if (av_data[k][0] != spec_data[j][2]) | |
continue; | |
Logger.log(average_price, av_data[k], spec_data[j][2]) | |
average_price = average_price + (av_data[k][2] * spec_data[j][4]); | |
} | |
} | |
average_price = average_price * data[i][4]; | |
from_sheet.getRange(i + 5, 12).setValue(average_price); | |
from_sheet.getRange(i + 5, 11).setValue(true); | |
} | |
data = to_sheet.getRange(5, 2, to_sheet.getLastRow(), 1).getValues(); | |
for (var i = 0; i < data.length; i++){ | |
if (!data[i][0]) | |
break; | |
} | |
i = i + 5; | |
var lastrow = i; | |
var rows = []; | |
var date = new Date(); | |
for (var key in consumes){ | |
var row = []; | |
row.push(date); | |
row.push("Расход"); | |
row.push(key); | |
row.push(""); | |
row.push(consumes[key]); | |
row.push(""); | |
row.push(false); | |
row.push(""); | |
row.push(""); | |
row.push("Добавлено автоматически при создании готовой продукции"); | |
rows.push(row); | |
i++; | |
} | |
if (rows.length) | |
to_sheet.getRange(lastrow, 1, rows.length, rows[0].length).setValues(rows); | |
close_form("Готово"); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment