Working with price lists in Google Sheets: automation via API

 pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

 from google.oauth2 import service_account from googleapiclient.discovery import build # Authorization via Service Account SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] SERVICE_ACCOUNT_FILE = 'credentials.json' credentials = service_account.Credentials.from_service_account_file( SERVICE_ACCOUNT_FILE, scopes=SCOPES) service = build('sheets', 'v4', credentials=credentials) # Table ID from URL SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' RANGE_NAME = 'Price!A2:E' # Read data sheet = service.spreadsheets() result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME).execute() values = result.get('values', []) if not values: print('Data not found.') else: for row in values: # Process each price line product_code = row[0] product_name = row[1] price = row[2] stock = row[3] print(f'{product_code}: {product_name} - {price} rub. ({stock} pcs.)')

 # Use scopes with edit permissions to write SCOPES = ['https://www.googleapis.com/auth/spreadsheets'] # Prepare data values = [ ['SKU123', 'Item 1', 1500, 100], ['SKU124', 'Item 2', 2500, 50], ['SKU125', 'Item 3', 3500, 25] ] body ={ 'values': values } # Write data result = service.spreadsheets().values().update( spreadsheetId=SPREADSHEET_ID, range='Price!A2:D4', valueInputOption='RAW', body=body).execute() print(f'{result.get("updatedCells")} cells updated.')

 function updatePricesFromAPI() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Price'); // URL of your API with prices var url = 'https://api.example.com/prices'; try { var response = UrlFetchApp.fetch(url, { 'method': 'get', 'headers':{ 'Authorization': 'Bearer YOUR_API_TOKEN' } }); var data = JSON.parse(response.getContentText()); // Clearing old data (starting from the 2nd row) var lastRow = sheet.getLastRow(); if (lastRow > 1) { sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn()).clearContent(); } // Write new data var row = 2; data.products.forEach(function(product) { sheet.getRange(row, 1).setValue(product.sku); sheet.getRange(row, 2).setValue(product.name); sheet.getRange(row, 3).setValue(product.price); sheet.getRange(row, 4).setValue(product.stock); sheet.getRange(row, 5).setValue(new Date()); row++; }); Logger.log('Price successfully updated: ' + (row - 2) + ' products'); } catch (e){ Logger.log('Ошибка обновления: ' + e.toString()); // Отправка уведомления на email MailApp.sendEmail( 'manager@example.com', 'Ошибка обновления прайса', 'Не удалось обновить прайс. Ошибка: ' + e.toString() ); } } // Function for setting up a trigger function setupTrigger() { // Delete old triggers var triggers = ScriptApp.getProjectTriggers(); triggers.forEach(function(trigger) { if (trigger.getHandlerFunction() === 'updatePricesFromAPI') { ScriptApp.deleteTrigger(trigger); } }); // Create a new trigger (every hour) ScriptApp.newTrigger('updatePricesFromAPI').timeBased().everyHours(1).create(); }

 function onEdit(e) { var sheet = e.source.getActiveSheet(); var range = e.range; // Check only the "Price" sheet if (sheet.getName()!== 'Price') return; // Check the price column (column 3) if (range.getColumn() === 3) { var value = range.getValue(); // Check for a number if (isNaN(value) || value< 0) { SpreadsheetApp.getUi().alert('Ошибка: цена должна быть положительным числом'); range.setValue(''); // Очищаем неверное значение return; } // Автоматический расчет цены с наценкой var markup = 1.3; // 30% наценка var retailPrice = value * markup; sheet.getRange(range.getRow(), 6).setValue(retailPrice.toFixed(2)); } // Проверка столбца с остатками (столбец 4) if (range.getColumn() === 4) { var stock = range.getValue(); if (isNaN(stock) || stock < 0 || stock % 1!== 0) { SpreadsheetApp.getUi().alert('Ошибка: остаток должен быть целым положительным числом'); range.setValue(''); return; } // Цветовая индикация остатков if (stock === 0) { range.setBackground('#ffcccc'); // Красный - нет на складе } else if (stock < 10) { range.setBackground('#fff3cd'); // Желтый - мало } else { range.setBackground('#d4edda'); // Зеленый - достаточно } } }

 // On the frontend of the site async function loadPricesFromSheet() { const SHEET_ID = 'YOUR_SHEET_ID'; const API_KEY = 'YOUR_API_KEY'; const RANGE = 'Price!A2:E'; const url = `https://sheets.googleapis.com/v4/spreadsheets/${SHEET_ID}/values/${RANGE}?key=${API_KEY}`; try { const response = await fetch(url); const data = await response.json(); const products = data.values.map(row => ({ sku: row[0], name: row[1], price: parseFloat(row[2]), stock: parseInt(row[3]), updated: row[4] } )); displayProducts(products); } catch (error){ console.error('Ошибка загрузки прайса:', error); } } function displayProducts(products) { const container = document.getElementById('products-container'); products.forEach(product => { const productHTML = `

 // Apps Script: Sending data to the website when changing function onEdit(e) { sendUpdateToWebsite(); } function sendUpdateToWebsite() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Price'); var data = sheet.getDataRange().getValues(); // Skipping the header var products = []; for (var i = 1; i< data.length; i++) { products.push({ sku: data[i][0], name: data[i][1], price: data[i][2], stock: data[i][3] }); } var payload = { products: products, timestamp: new Date().toISOString() }; var options = { method: 'post', contentType: 'application/json', payload: JSON.stringify(payload), headers: { 'X-API-Key': 'YOUR_WEBSITE_API_KEY' } }; try { var response = UrlFetchApp.fetch('https://your-website.com/api/update-prices', options); Logger.log('Данные отправлены: ' + response.getResponseCode()); } catch (e) { Logger.log('Ошибка отправки: ' + e.toString()); } }

 // Apps Script: publish CSV endpoint function doGet(e) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Price'); var data = sheet.getDataRange().getValues(); // Checking the API key if (e.parameter.key!== 'YOUR_SECRET_KEY') { return ContentService.createTextOutput('Unauthorized').setMimeType(ContentService.MimeType.TEXT); } // Generate CSV var csv = ''; data.forEach(function(row) { csv += row.join(',') + '\n'; }); return ContentService.createTextOutput(csv).setMimeType(ContentService.MimeType.CSV).downloadAsFile('price_list.csv'); }

 // Apps Script: Setting up conditional formatting function setupConditionalFormatting() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Price'); var range = sheet.getRange('D2:D1000'); // Column with remainders var rules = sheet.getConditionalFormatRules(); // Red: remainder = 0 var rule1 = SpreadsheetApp.newConditionalFormatRule().whenNumberEqualTo(0).setBackground('#f4cccc').setRanges([range]).build(); // Yellow: remainder< 10 var rule2 = SpreadsheetApp.newConditionalFormatRule().whenNumberLessThan(10).setBackground('#fff2cc').setRanges([range]).build(); // Зеленый: остаток > = 10 var rule3 = SpreadsheetApp.newConditionalFormatRule().whenNumberGreaterThanOrEqualTo(10).setBackground('#d9ead3').setRanges([range]).build(); rules.push(rule1, rule2, rule3); sheet.setConditionalFormatRules(rules); }

 // Apps Script: Notify the team about changes function notifyTeamOnPriceChange(e) { var sheet = e.source.getActiveSheet(); var range = e.range; // Track only the price column if (sheet.getName()!== 'Price' || range.getColumn()!== 3) return; var row = range.getRow(); var sku = sheet.getRange(row, 1).getValue(); var productName = sheet.getRange(row, 2).getValue(); var oldPrice = e.oldValue; var newPrice = e.value; var user = e.user.getEmail(); var message = ` Price change in the price list Product: ${productName} (${sku}) Old price: ${oldPrice} rubles New price: ${newPrice} rubles Changed by: ${user} Time: ${new Date().toLocaleString('ru-RU')} `; // Sending to Telegram sendToTelegram(message); // Sending to email var recipients = 'sales@example.com, manager@example.com'; MailApp.sendEmail(recipients, 'Price change in price list', message); } function sendToTelegram(message) { var token = 'YOUR_BOT_TOKEN'; var chatId = 'YOUR_CHAT_ID'; var url = `https://api.telegram.org/bot${token}/sendMessage`; var payload ={ chat_id: chatId, text: message }; UrlFetchApp.fetch(url,{ method: 'post', contentType: 'application/json', payload: JSON.stringify(payload) } ); }