Отправка данных из *.XLSX в Google Sheets

«Простота — залог надежности.»

Всем привет! Я обычный пользователь MS Excel и Google Docs, любитель-программист на VBA, App Script и JavaScript.

Задача

В данной статье хочу показать как можно быстро и легко отправить объемные данные из файла *.XLSX в Google Sheets при помощи VBA и App Script сохранив тем самым формат отправляемых данных(границы, заливки и прочее).

Цель статьи — поделиться доступным инструментом по отправки данных в Google Sheets из XLSX в автоматическом режиме, при помощи VBA и App Script.

Все инструменты реализованы стандартными средствами VBA, App Script без использования дополнительных библиотек. 

Подготовительный этап, в наличии имеем:

• Microsoft Excel

• Почтавая служба Outlook

• Почта Gmail

Практическая часть:

Алгоритм работы автоматической отправки данных с XLSX в Google Sheets заключается в следующем, при помощи VBA отправляем данные через почтовую службу Outlook на свою почту Gmail с определенной меткой, после чего App Script извлекает данные в Google Sheets документ.

В редактроре VBA создадим функцию и процедуру по отправки письма с вложением:

Function SendEmailOutlook(ByVal Email$, ByVal MailText$, Optional ByVal Subject$ = "", _                                Optional ByVal AttachFilename As Variant) As Boolean     On Error Resume Next: Err.Clear     Dim OA As Object: Set OA = CreateObject("Outlook.Application")     If OA Is Nothing Then MsgBox "Не удалось запустить OUTLOOK для отправки почты", vbCritical: Exit Function       With OA.CreateItem(0)   'создаем новое сообщение         .To = Email$: .Subject = Subject$: .Body = MailText$         If VarType(AttachFilename) = vbString Then .Attachments.Add AttachFilename         If VarType(AttachFilename) = vbObject Then    ' AttachFilename as Collection             For Each File In AttachFilename: .Attachments.Add File: Next         End If         For i = 1 To 100000: DoEvents: Next    ' без паузы не отправляются письма без вложений         Err.Clear: .send         SendEmailOutlook = Err = 0     End With     Set OutApp = Nothing End Function ________________________________________________________________________________ Sub sendMail()    'отправляем письмо с 1 вложением     attach$ = "Ваш_путь_до_файла\файл.xlsx"    ' прикрепляем текущий файл Excel     res = SendEmailOutlook("itkod2020@gmail.com", "", "Label_для_Gmail", attach$) End Sub

Далее, необходимо в редакторе App Script вашей Google таблицы создать две фукции.Первая, будет проверять входящие письма с «Вашей меткой». Вторая, по созданию новой книги в Google Drive с полученными данными Эксель, которая скопирует в ваш Google sheet и удалить более ненужную книгу с Google Drive.

function emailTrigger() {   var label = GmailApp.getUserLabelByName("Ваша_Метка в Gmail"); //метка на папку в гугл почта   if(label != null){     var threads = label.getThreads();     for (var i=0; i<threads.length; i++) {       getExcelFile(threads[i]);       threads[i].removeLabel(label);     }   } }

Функция для извлечения файла Excel

function getExcelFile(thread)  { //Функция для извлечения файла Excel      var messages = thread.getMessages(); //извлекает сообщения в первом потоке   var len = messages.length; //Получает количество сообщений в первом потоке   var message = messages[len-1] //получает первое сообщение в данном потоке   var attachments = message.getAttachments(); // Получает вложение первого сообщения       //Обработка пркрепленного файла   var xlsxBlob = attachments[0]; // Предполагается, что вложения[0] - это большой двоичный объект файла xlsx.   Logger.log(xlsxBlob.getContentType())   var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id; //Преобразует Excel в "Google Лист" на Google диске и получает идентификатор файла   var filename = xlsxBlob.getName(); //возвращает имя файла преобразованных файлов   var tabName = filename.substring(13).slice(0,filename.length-18); // обработайте строку имени файла только в дату, которая будет именем вкладки    var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // Расположение преобразованного файла Excel -> теперь google sheet var destination = SpreadsheetApp.openById("ID вашего Google Sheets"); //id гугл файла(из ссылки ) var sss=destination.getSheetByName("Имя листа в Google Sheets");//название гугл листа куда летят данные  //Удаление файла с Drive Google  Drive.Files.remove(convertedSpreadsheetId); labelName='Ваша_Метка в Gmail'; deleteForever(labelName); }  function deleteForever(labelName) {     var threads = GmailApp.search("in:trash label:" + labelName);     for (var i = 0; i < threads.length; i++) {       threads[i].moveToTrash();      } };

Настраиваем триггер как вам удобно: раз в минуты, каждый час и т.д.

Создаем в Gmail папку с меткой(Label) по которой App Script будет анализировать.На этом этапе завершается механизм получение и обработки данных.

Далее, можно запустить процедуру на VBA «Sub sendMail()» для проверки работоспособнисти механизма и использовать по своего целевому назначению.

Заключение

Реализуемый функционал взаимодействия Эксель с Google Sheets, по отправки и получения данных удобен и практичен как в его реализации так и в обработки значительных объемных данных.

Всем спасибо!

Алексей Соболев

Энтузиаст, программист-любитель