Отправка данных из *.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, по отправки и получения данных удобен и практичен как в его реализации так и в обработки значительных объемных данных.

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

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

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