Ошибка при преобразовании данных из Excel в CSV с использованием библиотеки SheetJS и скрипта Google Apps
У меня есть 6 файлов (5 xlsx, 1 csv) в папке Google Drive, которые я хочу импортировать в 6 разных вкладок Google Sheet. Танаике предложил решение здесь, которое хорошо работает, если размер файла небольшой. К сожалению, в моих файлах тысячи записей (около 50 000 записей) размером 4–5 МБ. Вот сценарий:
function importExcel1(file, sheet) {
// Library Key: 1B0eoHz03BVtSZhJAocaGNq94RjoXocz8xGMaLzwVdmAvYW5k8s5Yd360
// Retrieve values from XLSX file.
const MD = MicrosoftDocsApp.setFileId(file.getId());
const srcSS = MD.getSpreadsheet();
const values = srcSS.getSheets()[0].getDataRange().getValues();
// console.info(values); // Confirm the retrieved values in the log.
MD.end(); // When this line is run, the Spreadsheet created as a temporal file from the XLSX file is removed.
if (values.length > 0) {
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}
}
Он быстро получает данные из файлов Excel, но при установке значений на вкладках возникает следующая ошибка:
Функция (импортExcel1): Ошибка: превышено максимальное время выполнения.
Я попробовал другой подход с SheetJS Library — код адаптирован из решения Танаике:
function importFiles() {
var folderId = 'folder ID'; // ID of the folder where files are stored
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var ss = SpreadsheetApp.getActiveSpreadsheet();
while (files.hasNext()) {
var file = files.next();
var fileName = file.getName();
// Skip files that already have "Done" in the name
if (fileName.includes('Done')) {
console.info(`Skipping already processed file: ${fileName}`);
continue;
}
var fileType = fileName.slice(0, 6).toLowerCase();
var sheet;
var range;
switch (fileType) {
case 'parcel':
sheet = ss.getSheetByName("import - shipping");
range = 'A:T';
break;
case 'kit-re':
sheet = ss.getSheetByName("import - kitting");
range = 'A:G';
break;
case 'req-re':
sheet = ss.getSheetByName("import - orders");
range = 'A:U';
break;
case 'billin':
sheet = ss.getSheetByName("import - billing codes");
range = 'A:G';
break;
case 'req_li':
sheet = ss.getSheetByName("import - order line");
range = 'A:G';
break;
case 'packag':
if (fileName.endsWith('.csv')) {
sheet = ss.getSheetByName("Manual Extract - Package Grid");
range = 'A:AO';
}
break;
default:
continue; // Skip files that do not match the criteria
}
if (sheet && range) {
sheet.getRange(range).clearContent();
SpreadsheetApp.flush();
console.info("Sheet: " + sheet.getName());
console.info("File: " + file.getName());
if (file.getMimeType() === MimeType.CSV) {
importCSV(file, sheet);
} else {
convertExcelToCSV(file, sheet);
}
// Rename the file by adding "Done" to the end of its name
// file.setName(fileName + ' Done');
// console.info(`Processed and renamed file: ${fileName} to ${file.getName()}`);
}
}
}
function importCSV(dataOrFile, sheet) {
var csvData;
if (typeof dataOrFile === 'string') {
// If dataOrFile is a string, treat it as CSV data
csvData = dataOrFile;
} else {
// If dataOrFile is a file, extract the data as a string
csvData = dataOrFile.getBlob().getDataAsString();
}
var sheetId = sheet.getSheetId();
console.info(sheetId);
var resource = {
requests: [
{
pasteData: {
data: csvData,
coordinate: { sheetId: sheetId },
delimiter: ","
}
}
]
};
Sheets.Spreadsheets.batchUpdate(resource, 'Spreadsheet ID');
}
function convertExcelToCSV(file, sheet) {
// Get the Excel file as a blob
var excelBlob = DriveApp.getFileById(file.getId()).getBlob();
// Convert the Blob into a byte array and read it using the XLSX library
var data = new Uint8Array(excelBlob.getBytes());
var workbook = XLSX.read(data, { type: 'array' });
// Get the first sheet from the workbook
var firstSheetName = workbook.SheetNames[0];
var firstSheet = workbook.Sheets[firstSheetName];
var csvData = XLSX.utils.sheet_to_csv(firstSheet);
// Import the CSV data into the Google Sheet
importCSV(csvData, sheet);
}
Я не уверен, что он получает данные более эффективно, потому что я продолжаю получать эту ошибку:
Ошибка: неверный столбец -1.
encode_col @ SheetJS.gs:4454
sheet_to_csv @ SheetJS.gs:27507
Есть ли другой способ сделать запрос batch более надежным? Любая помощь в устранении этой ошибки будет очень признательна?
Проблема связана с методом setValues: обновление вкладки соответствующими данными требует времени. Нам нужно использовать пакетную обработку, чтобы улучшить его. Вот так: stackoverflow.com/questions/68610298/…
@TheMaster, я обновил ошибку для библиотеки SheetJS. Я получаю эту ошибку для всех 5 файлов xlsx
Есть ли в файле пустой лист (возможно, скрытый?)?
Не пустой лист, но я понял, что есть некоторые файлы xlsx, в которых нет пустых столбцов в конце, я добавил пустой столбец в конце, и все прошло успешно.
Если возможно, поделитесь небольшим файлом xlsx, который воспроизводит ту же ошибку (после удаления конфиденциальных данных и удаления нескольких строк и столбцов, а также удаления всех листов, кроме одного, т. е. минимального файла, необходимого для воспроизведения ошибки).
Вот ссылка на образец файла xlsx, который выдает сообщение об ошибке при попытке преобразовать его в csv: docs.google.com/spreadsheets/d/…
Во-первых, я глубоко извиняюсь за то, что мой ответ не пригодился в вашей ситуации. Что касается Here is the link to xlsx sample file which produces an error message when trying to convert it into csv:, если вы использовали нижний скрипт, то, к сожалению, когда я протестировал ваш скрипт с использованием предоставленного вами файла XLSX, ошибок не возникло. values из const values = Utilities.parseCsv(csv); имеют действительные значения без ошибок. Итак, можете ли вы предоставить свой текущий сценарий для его правильного копирования?
Спасибо за ответ, только что протестировал скрипт, после удаления пустых столбцов из последних все файлы обрабатываются. Можете ли вы протестировать скрипт в этом файле: docs.google.com/spreadsheets/d/…
Кроме того, не могли бы вы объяснить, почему возникает эта ошибка? Ошибка отображала столбец: -1, хотя все файлы xlsx имели более 5 столбцов.
Спасибо за ответ. Что касается Thank you for your reply, I just tested the script, after removing empty columns from the last, all files are processed. Can you please test the script on this file: docs.google.com/spreadsheets/d/…, когда я проверил предоставленный вами файл, ошибок не возникло. Приношу извинения за эту ситуацию. Итак, можете ли вы предоставить свой текущий сценарий для его правильного копирования?
Кстати, насчет Unfortunately, my files have thousands of entries (approximately 50000 entries) with file size of 4mb-5mb. Here is the script:, в данном случае, где в вашем скрипте показа строка с ошибкой? Меня беспокоит, что в вашей ситуации может потребоваться использовать Sheets API вместо службы электронных таблиц (SpreadsheetApp).
По сути, я хочу обновить все данные файла xlsx (без предварительного преобразования в CSV) в лист Google с помощью API Таблиц, чтобы минимизировать время выполнения. Можем ли мы обновить данные непосредственно из xlsx в лист?
Спасибо за ответ. Что касается can we update the data directly from xlsx to sheet?, я должен извиниться за текущую спецификацию. На текущем этапе Sheets API можно использовать для электронных таблиц Google. К сожалению, Sheets API нельзя напрямую использовать для данных XLSX. В этом случае необходимо преобразовать данные XLSX в электронную таблицу Google. Благодаря этому значения можно использовать с API Таблиц.
Можете ли вы предложить лучший подход? 1. преобразовать данные xlsx в электронную таблицу, а затем использовать Sheets API. 2. преобразовать xlsx в csv и затем использовать эти данные с помощью Sheets API.
Спасибо за ответ. Что касается Can you please suggest a better approach? 1. convert xlsx data to Spreadsheet and then Use Sheets API 2. converting the xlsx to csv and then use this data with Sheets API, в случае преобразования XLSX и CXSV в электронную таблицу Google это можно сделать с помощью Drive API. Таким образом, преобразованную электронную таблицу можно использовать с API Sheets. Но есть важный момент.
В вашей ситуации, какое общее количество ячеек входит в I have 6 files (5 xlsx, 1 csv) in Google Drive folder? Если оно превышает 10 000 000, ваша цель I want to import into 6 different tabs of Google Sheet. не может быть достигнута. Я тоже беспокоюсь об этом.
К счастью, количество ячеек меньше 10 000 000. У меня есть 5 файлов в формате xlsx, поэтому мне следует сначала преобразовать их в CSV, а затем использовать SheetsAPI, или лучше сначала преобразовать их в электронную таблицу Google, а затем использовать API Sheets для вставки значений во вкладки?
Спасибо за ответ. Я понял ваше общее количество клеток. О порядке действий: 1. Конвертируйте файлы XLSX и CSV в таблицы Google. 2. Получите все значения из преобразованных электронных таблиц. (Каждый файл содержит только одну вкладку?) 3. Поместите значения на каждый лист целевой таблицы.
да, это идеальный рабочий процесс. Можете ли вы предложить решение/модификацию для этого? Я также добавил свой полный код к вопросу
Спасибо за ответ. Я должен извиниться за мое плохое знание английского языка. К сожалению, я не смогла понять ответ на свой вопрос Each file includes only one tab?.
это означает, что данные из каждого файла будут поступать на каждую вкладку, поэтому данные из 6 разных файлов будут поступать на 6 разных вкладок листа Google, приведенный выше сценарий в вопросе показывает этот подход
Спасибо за ответ. Из вашего ответа я предложил ответ. Пожалуйста, подтвердите это. Если это было не полезно, прошу прощения.
Ответы 1
В вашей ситуации как насчет следующей модификации?
В этом модифицированном сценарии выполняются следующие шаги.
- Преобразуйте данные XLSX или CSV в электронную таблицу Google.
- Получите значения из преобразованной электронной таблицы с помощью Sheets API.
- Поместите значения на каждый лист целевой таблицы.
- Удалите преобразованную таблицу.
Прежде чем использовать этот скрипт, включите Drive API v3 и Sheets API в расширенных службах Google. И, пожалуйста, установите идентификатор папки.
Я догадался, что в вашей ситуации вы хотите получить значения из первого листа каждого XLSX.
Модифицированный сценарий 1:
В этом сценарии после получения всех значений они помещаются на каждый лист.
function importFiles() {
var folderId = '###'; // ID of the folder where files are stored
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles(); // or const files = DriveApp.getFolderById(folderId).searchFiles(`mimeType='${MimeType.MICROSOFT_EXCEL}' or mimeType='${MimeType.CSV}' and trashed=false`);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = []; // Added
while (files.hasNext()) {
var file = files.next();
var fileName = file.getName();
// Skip files that already have "Done" in the name
if (fileName.includes('Done')) {
console.info(`Skipping already processed file: ${fileName}`);
continue;
}
var fileType = fileName.slice(0, 6).toLowerCase();
var sheet;
var range;
switch (fileType) {
case 'parcel':
sheet = ss.getSheetByName("import - shipping");
range = 'A:T';
break;
case 'kit-re':
sheet = ss.getSheetByName("import - kitting");
range = 'A:G';
break;
case 'req-re':
sheet = ss.getSheetByName("import - orders");
range = 'A:U';
break;
case 'billin':
sheet = ss.getSheetByName("import - billing codes");
range = 'A:G';
break;
case 'req_li':
sheet = ss.getSheetByName("import - order line");
range = 'A:G';
break;
case 'packag':
if (fileName.endsWith('.csv')) {
sheet = ss.getSheetByName("Manual Extract - Package Grid");
range = 'A:AO';
}
break;
default:
continue; // Skip files that do not match the criteria
}
if (sheet && range) {
sheet.getRange(range).clearContent();
SpreadsheetApp.flush();
console.info("Sheet: " + sheet.getName());
console.info("File: " + file.getName());
// I modified the below script.
const id = Drive.Files.copy({ mimeType: MimeType.GOOGLE_SHEETS }, file.getId()).id;
const { values } = Sheets.Spreadsheets.Values.get(id, "A:ZZZ");
data.push({ values, range: sheet.getName() });
Drive.Files.remove(id);
}
}
if (data.length == 0) return;
Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, ss.getId());
}
Модифицированный сценарий 2:
В этом сценарии каждое значение каждого файла помещается в каждый лист цикла while.
function importFiles() {
var folderId = '###'; // ID of the folder where files are stored
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var ss = SpreadsheetApp.getActiveSpreadsheet();
while (files.hasNext()) {
var file = files.next();
var fileName = file.getName();
// Skip files that already have "Done" in the name
if (fileName.includes('Done')) {
console.info(`Skipping already processed file: ${fileName}`);
continue;
}
var fileType = fileName.slice(0, 6).toLowerCase();
var sheet;
var range;
switch (fileType) {
case 'parcel':
sheet = ss.getSheetByName("import - shipping");
range = 'A:T';
break;
case 'kit-re':
sheet = ss.getSheetByName("import - kitting");
range = 'A:G';
break;
case 'req-re':
sheet = ss.getSheetByName("import - orders");
range = 'A:U';
break;
case 'billin':
sheet = ss.getSheetByName("import - billing codes");
range = 'A:G';
break;
case 'req_li':
sheet = ss.getSheetByName("import - order line");
range = 'A:G';
break;
case 'packag':
if (fileName.endsWith('.csv')) {
sheet = ss.getSheetByName("Manual Extract - Package Grid");
range = 'A:AO';
}
break;
default:
continue; // Skip files that do not match the criteria
}
if (sheet && range) {
sheet.getRange(range).clearContent();
SpreadsheetApp.flush();
console.info("Sheet: " + sheet.getName());
console.info("File: " + file.getName());
const id = Drive.Files.copy({ mimeType: MimeType.GOOGLE_SHEETS }, file.getId()).id;
const { values } = Sheets.Spreadsheets.Values.get(id, "A:ZZZ");
if (values.length == 0) continue;
Sheets.Spreadsheets.Values.update({ values }, ss.getId(), sheet.getName(), { valueInputOption: "USER_ENTERED" });
Drive.Files.remove(id);
// Utilities.sleep(3000); // This line might be required to be used.
}
}
}
Ссылки:
- Метод: files.copy
- Метод: электронная таблица.values.get
- Метод: Spreadsheets.values.batchUpdate
- Метод: электронная таблица.значения.обновление
Спасибо за ответ, первое решение мне нравится гораздо больше. Он завершил свое выполнение за 2 минуты. Замечательно...
1. Вы можете попробовать использовать полный файл js (git.sheetjs.com/sheetjs/sheetjs/src/branch/master/xlsx.js) вместо свернутого файла, чтобы улучшить журнал ошибок. 2. Все ли 5 файлов xlsx выдают ошибкуsheetjs? Попробуйте использовать только один простой файл xlsx, чтобы проверить, импортируется ли он должным образом с помощьюsheetjs.