Why use Google Sheets
Problem
Sellers often work with detailed sales reports. Manually collecting data increases the risk of errors and slows down the analysis process, which can negatively impact decision-making speed.
Solution
Use the WB API to automatically export data to Google Sheets, reducing the risk of errors and saving time.
Result
Set up regular delivery of an up-to-date and accurate sales report in the form of a Google Sheet.
Advantages
- Efficiency. Reduces manual labor.
- Accuracy. Minimizes the risk of errors.
- Scalability. Works with large volumes of data.
- Relevance. Fresh data will always be at hand.
In this article, we will explain how to set up this integration.
Step 1: Generate API Token in seller’s account
- Go to Settings → Users and find the number with the owner status.
- Log in to your account with this number to generate an API token.
- Go to Settings → API Access, enter any characters in the "Token Name" field. Select the token type "Statistics" and click "Create Token". Save the token, as it will only be shown once.
Step 2: Set up Google Sheets
- Open Google Sheets and create a new document.
- Expand the "Extensions" menu and select "Apps Script".
Step 3: Create a script to export data
- Create a new project in the script editor and paste the ready-made code.
- Replace
YOUR_API_TOKEN
with your own token. - In the
var baseUrl
parameter, set the report date range, for example,dateFrom=2024-01-29&dateTo=2025-01-01
. - Name the script, for example, "Sales Report".
- After setup, click the "💾" button.
Note: Data is available starting from January 29, 2024.
function getDataFromAPI() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var token = 'ВАШ_API_ТОКЕН';
var limit = 100000;
var baseUrl = 'https://statistics-api.wildberries.ru/api/v5/supplier/reportDetailByPeriod?dateFrom=2024-01-29&dateTo=2025-01-01&limit=' + limit;
var lastRow = sheet.getLastRow();
var headers = [];
var rrdid = 0;
if (lastRow > 1) {
headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var rrdidIndex = headers.indexOf('rrd_id');
if (rrdidIndex !== -1) {
rrdid = sheet.getRange(lastRow, rrdidIndex + 1).getValue();
} else {
throw new Error("Колонка 'rrd_id' не найдена в заголовках.");
}
} else if (lastRow === 1) {
headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
}
var data, response, url;
var allData = [];
var headersSet = (headers.length > 0);
while (true) {
url = baseUrl + '&rrdid=' + rrdid;
var encodedUrl = encodeURI(url);
var options = {
'method': 'get',
'headers': {
'Authorization': token
},
'muteHttpExceptions': true
};
try {
response = UrlFetchApp.fetch(encodedUrl, options);
if (response.getResponseCode() !== 200) {
throw new Error('Ошибка API: ' + response.getContentText());
}
data = JSON.parse(response.getContentText());
if (data === null || data.length === 0) {
Logger.log('Нет больше данных для выгрузки.');
break;
}
if (!headersSet) {
headers = Object.keys(data[0]);
sheet.appendRow(headers);
headersSet = true;
}
data.forEach(function(row) {
var values = headers.map(function(header) {
return row[header];
});
allData.push(values);
});
rrdid = data[data.length - 1].rrd_id;
Logger.log('Следующий rrdid: ' + rrdid);
if (data.length < limit) {
Logger.log('Выгружены все доступные данные.');
break;
}
} catch (error) {
Logger.log('Ошибка при запросе данных: ' + error.message);
break;
}
}
if (allData.length > 0) {
sheet.getRange(sheet.getLastRow() + 1, 1, allData.length, allData[0].length).setValues(allData);
}
}
Step 4: Run the script
- Click "▶️ Run" to execute the script.
- The system will request permission to run the script. To grant it:
- Click "Review Permissions" in the popup window,
- Then select your account, click "Advanced Settings",
- Then "Go to page...", → "Allow".
- Return to the created sheet, where you will see your report.
Step 5: Set up automatic periodic export
- In Apps Script, select "⏰ Triggers".
- Click "+ Add Trigger" and configure the fields as follows:
- "Select function" —
getDataFromAPI
- "Select event source" — "Time-driven trigger".
- "Select type of time trigger" — "Daily".
- In the "Select time" field, set a convenient time for you (for example, every day at 23:00).
- Click "Save".
Build integrations with WB API
Automatic data collection provides sellers with a significant advantage and is becoming increasingly indispensable for successful business management.