Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
- var lastRow = sheet.getLastRow();
- var reportCurrency = "";
- // @zerohaste вместе с нейросетью написал это дерьмо. удаление моей всратой рекламы преследуется кармически.
- function onOpen() {
- var ui = SpreadsheetApp.getUi();
- ui.createMenu('Paypal Ge Taxes')
- .addItem('Calculate USD', 'calculateUSD')
- .addItem('Calculate EUR', 'calculateEUR')
- .addToUi();
- }
- function calculateUSD() {
- executeAllScripts("USD");
- }
- function calculateEUR() {
- executeAllScripts("EUR");
- }
- function executeAllScripts(currency) {
- reportCurrency = currency;
- sheet.getRange("G1").setValue(currency);
- setHeaders();
- getExchangeRate();
- multiplyColumns();
- }
- function setHeaders() {
- sheet.getRange("D2:D" + lastRow).clearContent();
- sheet.getRange("E2:E" + lastRow).clearContent();
- sheet.getRange("F2:F" + lastRow).clearContent();
- sheet.getRange("G2:G" + lastRow).clearContent();
- sheet.getRange("D1").setValue("Rate " + reportCurrency);
- sheet.getRange("E1").setValue("In Lari");
- sheet.getRange("F1").setValue("Api Date");
- sheet.getRange("H1").setFormula('=HYPERLINK("https://mapi.ge")');
- sheet.getRange("I1").setValue("Exchange rates for online/offline");
- sheet.getRange("D" + lastRow).setValue("Sum in Lari");
- sheet.getRange("A" + lastRow).setValue("Mapi.ge - exchange rates Tbilisi/Batumi");
- }
- function getExchangeRate() {
- var rangeA = sheet.getRange("A2:A" + lastRow);
- var dates = rangeA.getValues();
- for (var i = 0; i < dates.length; i++) {
- var dateString = dates[i][0];
- if (!isValidDate(dateString)) {
- continue;
- }
- var formattedDate = formatDate(dateString);
- var apiUrl = getApiUrl(reportCurrency, formattedDate);
- sheet.getRange("G" + (i + 2)).setValue(apiUrl);
- var response = UrlFetchApp.fetch(apiUrl);
- var data = JSON.parse(response.getContentText());
- try {
- var rate = data[0].currencies[0].rate;
- sheet.getRange("D" + (i + 2)).setValue(rate);
- } catch (error) {
- sheet.getRange("D" + (i + 2)).setValue("N/A");
- }
- writeApiDate(sheet, formattedDate, i + 2);
- Utilities.sleep(10);
- }
- }
- function isValidDate(dateString) {
- return /^\d{1,2}\/\d{1,2}\/\d{4}$/.test(dateString);
- }
- function formatDate(dateString) {
- var parts = dateString.split("/");
- return parts[2] + "-" + parts[1] + "-" + parts[0];
- }
- function getApiUrl(reportCurrency, date) {
- return "https://nbg.gov.ge/gw/api/ct/monetarypolicy/currencies/en/json/?currencies=" + reportCurrency + "&date=" + date;
- }
- function writeApiDate(sheet, date, row) {
- var formattedDate = Utilities.formatDate(new Date(date), sheet.getParent().getSpreadsheetTimeZone(), "dd/MM/yyyy");
- sheet.getRange("F" + row).setValue(formattedDate);
- }
- function multiplyColumns() {
- var rangeC = sheet.getRange("C2:C" + lastRow);
- var rangeD = sheet.getRange("D2:D" + lastRow);
- var rangeE = sheet.getRange("E2:E" + (lastRow - 1));
- var valuesC = rangeC.getValues();
- var valuesD = rangeD.getValues();
- for (var i = 0; i < valuesC.length; i++) {
- var amount = valuesC[i][0];
- var rate = valuesD[i][0];
- // Заменяем запятую на точку в значениях столбцов C и D
- amount = parseFloat(amount.toString().replace(",", "."));
- rate = parseFloat(rate.toString().replace(",", "."));
- var result = amount * rate;
- // Проверка, является ли результат умножения NaN или равен 0
- if (isNaN(result) || result === 0) {
- continue; // Пропустить запись значения, если результат NaN или равен 0
- }
- rangeE.getCell(i + 1, 1).setValue(result);
- }
- // Получение суммы столбца E
- var sumE = "=SUM(E2:E" + (lastRow - 1) + ")";
- sheet.getRange("E" + lastRow).setValue(sumE);
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement