Друзья, нужно сделать, чтобы данные из постоянно обновляемого xlsx файла на google диске подтягивались в таблицу Google. Проблема в том, что прост из xlsx файла данные не подтягиваются, нужно преобразовывать в формат google sheets, но xlsx постоянно обновляемый и при преобразовании в преобразованный файл данные уже не обновляются. Есть ли решение?))) Очень надо, огромное спасибо!)))
Друзья, нужно сделать, чтобы данные из постоянно обновляемого xlsx файла на google диске подтягивались в таблицу Google. Проблема в том, что прост из xlsx файла данные не подтягиваются, нужно преобразовывать в формат google sheets, но xlsx постоянно обновляемый и при преобразовании в преобразованный файл данные уже не обновляются. Есть ли решение?))) Очень надо, огромное спасибо!)))rexar
Если работать надо со стороны GoogleSpreadsheets - воспользуйтесь имеющимися дополнениями для гугл-таблиц. Например, SheetGO вполне себе так нормально импортирует данные из внешних источников в облаке (другие таблицы/Excel-файлы/CSV-файлы..., расписание встроено)
Не обязательно гонять данные через почту
Если работать надо со стороны GoogleSpreadsheets - воспользуйтесь имеющимися дополнениями для гугл-таблиц. Например, SheetGO вполне себе так нормально импортирует данные из внешних источников в облаке (другие таблицы/Excel-файлы/CSV-файлы..., расписание встроено)AndreTM
Если у вас уже всё доавтоматизировано до того, что и xlsx-файл на гуглодиск автоматически заливается - то вам проще рыть именно в сторону гугл-таблиц-проекта с кодом, который будет на это ориентироваться. По алгоритму "проверяем обновление некоторого известного файла на гугл-диске - импортируем этот xlsx в гуглтаблицу (кодом!) - [копируем данные из полученной книги в нужные места-источники данных нашей книги]/[или имеем уже в нашей книге заранее настроенный ссылки именно на эту пересоздаваемую гуглтаблицу]".
Если у вас уже всё доавтоматизировано до того, что и xlsx-файл на гуглодиск автоматически заливается - то вам проще рыть именно в сторону гугл-таблиц-проекта с кодом, который будет на это ориентироваться. По алгоритму "проверяем обновление некоторого известного файла на гугл-диске - импортируем этот xlsx в гуглтаблицу (кодом!) - [копируем данные из полученной книги в нужные места-источники данных нашей книги]/[или имеем уже в нашей книге заранее настроенный ссылки именно на эту пересоздаваемую гуглтаблицу]".AndreTM
доавтоматизировано до того, что и xlsx-файл на гуглодиск автоматически заливается
Ой, сильно сомневаюсь, что есть какой-то простой код (на любом языке!), позволяющий это сделать (не вручную!). Я вроде слежу за этим довольно внимательно, но на сегодня мне неизвестен какой-либо открытый API Google на эту тему. Если бы всё было так просто, то не появлялись бы инструменты типа SheetGO. Но, если что, если что-то конструктивно расскажете и покажете, то буду НЕОПИСУЕМО рад ошибиться в своих суждениях!
доавтоматизировано до того, что и xlsx-файл на гуглодиск автоматически заливается
Ой, сильно сомневаюсь, что есть какой-то простой код (на любом языке!), позволяющий это сделать (не вручную!). Я вроде слежу за этим довольно внимательно, но на сегодня мне неизвестен какой-либо открытый API Google на эту тему. Если бы всё было так просто, то не появлялись бы инструменты типа SheetGO. Но, если что, если что-то конструктивно расскажете и покажете, то буду НЕОПИСУЕМО рад ошибиться в своих суждениях!
есть ли подробное решение? Попробовал, не получилось...
На данный момент реализованного решения нет, но можно попробовать совместными усилиями, так сказать, для грядущих поколений.
Можете как-то показать свои неполучившиеся попытки? Значения ячеек заданного диапазона Excel уже умеете в длинную строку сворачивать и в тело письма Outlook ее помещать? Если да, то уже полдела сделано! На стороне Google я потом помогу.
есть ли подробное решение? Попробовал, не получилось...
На данный момент реализованного решения нет, но можно попробовать совместными усилиями, так сказать, для грядущих поколений.
Можете как-то показать свои неполучившиеся попытки? Значения ячеек заданного диапазона Excel уже умеете в длинную строку сворачивать и в тело письма Outlook ее помещать? Если да, то уже полдела сделано! На стороне Google я потом помогу.Gustav
Gustav, пока в тело письма помещать не получилось, отправляется только вложением. Разбираюсь как это реализовать. Читал, что есть вариант в автоматическом режиме открывать word, туда копируется значение диапазона ячеек, далее копируется текст word и вставляется в outlook, но я считаю, что это уже очень сложная схема)
Gustav, пока в тело письма помещать не получилось, отправляется только вложением. Разбираюсь как это реализовать. Читал, что есть вариант в автоматическом режиме открывать word, туда копируется значение диапазона ячеек, далее копируется текст word и вставляется в outlook, но я считаю, что это уже очень сложная схема)rexar
rexar, ещё есть вариант, сохранять excel в html, автоматически выгружать на хостинг и импортировать оттуда данные в google sheets, но это как я считаю не вариант
rexar, ещё есть вариант, сохранять excel в html, автоматически выгружать на хостинг и импортировать оттуда данные в google sheets, но это как я считаю не вариантrexar
Код отправки excel в теле письма нашёл!))) Теперь осталось вытянуть в таблицу!!!)))
[vba]
Код
Sub Mail_Selection_Range_Outlook_Body() 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm 'Don't forget to copy the function RangetoHTML in the module. 'Working in Excel 2000-2016 Dim rng As Range Dim OutApp As Object Dim OutMail As Object
Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Selection.SpecialCells(xlCellTypeVisible) 'Тут указываем лист и диапазон Set rng = Sheets("название листа").Range("E41:S190").SpecialCells(xlCellTypeVisible) On Error GoTo 0
If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If
With Application .EnableEvents = False .ScreenUpdating = False End With
Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0)
On Error Resume Next With OutMail .To = "mymail@gmail.com" .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Send 'or use .Display End With On Error GoTo 0
With Application .EnableEvents = True .ScreenUpdating = True End With
Set OutMail = Nothing Set OutApp = Nothing End Sub
Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2016 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook
'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With
'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With
'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.readall ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=")
'Close TempWB TempWB.Close savechanges:=False
'Delete the htm file we used in this function Kill TempFile
Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function
[/vba]
Gustav,
Код отправки excel в теле письма нашёл!))) Теперь осталось вытянуть в таблицу!!!)))
[vba]
Код
Sub Mail_Selection_Range_Outlook_Body() 'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm 'Don't forget to copy the function RangetoHTML in the module. 'Working in Excel 2000-2016 Dim rng As Range Dim OutApp As Object Dim OutMail As Object
Set rng = Nothing On Error Resume Next 'Only the visible cells in the selection Set rng = Selection.SpecialCells(xlCellTypeVisible) 'Тут указываем лист и диапазон Set rng = Sheets("название листа").Range("E41:S190").SpecialCells(xlCellTypeVisible) On Error GoTo 0
If rng Is Nothing Then MsgBox "The selection is not a range or the sheet is protected" & _ vbNewLine & "please correct and try again.", vbOKOnly Exit Sub End If
With Application .EnableEvents = False .ScreenUpdating = False End With
Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0)
On Error Resume Next With OutMail .To = "mymail@gmail.com" .CC = "" .BCC = "" .Subject = "This is the Subject line" .HTMLBody = RangetoHTML(rng) .Send 'or use .Display End With On Error GoTo 0
With Application .EnableEvents = True .ScreenUpdating = True End With
Set OutMail = Nothing Set OutApp = Nothing End Sub
Function RangetoHTML(rng As Range) ' Changed by Ron de Bruin 28-Oct-2006 ' Working in Office 2000-2016 Dim fso As Object Dim ts As Object Dim TempFile As String Dim TempWB As Workbook
'Copy the range and create a new workbook to past the data in rng.Copy Set TempWB = Workbooks.Add(1) With TempWB.Sheets(1) .Cells(1).PasteSpecial Paste:=8 .Cells(1).PasteSpecial xlPasteValues, , False, False .Cells(1).PasteSpecial xlPasteFormats, , False, False .Cells(1).Select Application.CutCopyMode = False On Error Resume Next .DrawingObjects.Visible = True .DrawingObjects.Delete On Error GoTo 0 End With
'Publish the sheet to a htm file With TempWB.PublishObjects.Add( _ SourceType:=xlSourceRange, _ Filename:=TempFile, _ Sheet:=TempWB.Sheets(1).Name, _ Source:=TempWB.Sheets(1).UsedRange.Address, _ HtmlType:=xlHtmlStatic) .Publish (True) End With
'Read all data from the htm file into RangetoHTML Set fso = CreateObject("Scripting.FileSystemObject") Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2) RangetoHTML = ts.readall ts.Close RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _ "align=left x:publishsource=")
'Close TempWB TempWB.Close savechanges:=False
'Delete the htm file we used in this function Kill TempFile
Set ts = Nothing Set fso = Nothing Set TempWB = Nothing End Function
У меня на стороне Windows (VBA) вот так получилось: [vba]
Код
Sub sendMail()
Dim outApp As Object 'Outlook.Application Dim outMail As Object 'Outlook.MailItem Dim myRange As Range
Const ol_MailItem As Long = 0 'olMailItem
Set myRange = Range("A1:C5") 'задаем диапазон Excel, из которого экспортируем значения
Set outApp = CreateObject("Outlook.Application") 'New Outlook.Application Set outMail = outApp.CreateItem(ol_MailItem)
With outMail .To = ".....@gmail.com" 'почтовый ящик на GMail .Subject = getSubject 'уникальный идентификатор отправки (для последующего контроля в Google) .Body = getBody(myRange) 'текстовое содержание заданного диапазона Excel .Display End With
Set outMail = Nothing Set outApp = Nothing End Sub
Function getSubject() As String 'уникальный идентификатор отправки getSubject = "add" & Format(Now, "_yyyymmdd_hhnnss_") & Right(Format(Timer, "#0.00"), 2) End Function
Function getBody(rng As Range) As String 'текстовое содержание диапазона Excel Dim a Dim i As Integer Dim j As Integer Dim strAll As String Dim strRow As String
Const rowSep As String = vbCrLf 'общепринятый разделитель строк - перевод строки Const colSep As String = "~" 'необычный разделитель колонок (ибо обычный vbTab превращается в серию пробелов)
a = rng.Value
strAll = "" For i = 1 To UBound(a, 1) strRow = "" For j = 1 To UBound(a, 2) strRow = strRow & colSep & a(i, j) Next j strRow = Mid(strRow, 2) strAll = strAll & rowSep & strRow Next i strAll = Mid(strAll, 3) getBody = strAll 'длинная-предлинная строка End Function
[/vba]
Тело письма получается примерно следующего вида (три колонки: число, текст и дата): [vba]
Начинаю думать про сторону Google (принимающую)...
У меня на стороне Windows (VBA) вот так получилось: [vba]
Код
Sub sendMail()
Dim outApp As Object 'Outlook.Application Dim outMail As Object 'Outlook.MailItem Dim myRange As Range
Const ol_MailItem As Long = 0 'olMailItem
Set myRange = Range("A1:C5") 'задаем диапазон Excel, из которого экспортируем значения
Set outApp = CreateObject("Outlook.Application") 'New Outlook.Application Set outMail = outApp.CreateItem(ol_MailItem)
With outMail .To = ".....@gmail.com" 'почтовый ящик на GMail .Subject = getSubject 'уникальный идентификатор отправки (для последующего контроля в Google) .Body = getBody(myRange) 'текстовое содержание заданного диапазона Excel .Display End With
Set outMail = Nothing Set outApp = Nothing End Sub
Function getSubject() As String 'уникальный идентификатор отправки getSubject = "add" & Format(Now, "_yyyymmdd_hhnnss_") & Right(Format(Timer, "#0.00"), 2) End Function
Function getBody(rng As Range) As String 'текстовое содержание диапазона Excel Dim a Dim i As Integer Dim j As Integer Dim strAll As String Dim strRow As String
Const rowSep As String = vbCrLf 'общепринятый разделитель строк - перевод строки Const colSep As String = "~" 'необычный разделитель колонок (ибо обычный vbTab превращается в серию пробелов)
a = rng.Value
strAll = "" For i = 1 To UBound(a, 1) strRow = "" For j = 1 To UBound(a, 2) strRow = strRow & colSep & a(i, j) Next j strRow = Mid(strRow, 2) strAll = strAll & rowSep & strRow Next i strAll = Mid(strAll, 3) getBody = strAll 'длинная-предлинная строка End Function
[/vba]
Тело письма получается примерно следующего вида (три колонки: число, текст и дата): [vba]
Можно файл excel на гугл диске конвертировать в гугл таблицу, прочитать его и потом грохнуть. Есть такой скрипт для конвертации в инете.Рабочий, проверил
[vba]
Код
function testConvertExcel2Sheets() { var xlsId = "0B3........lU"; // ID Excel файла на гугл диске var xlsFile = DriveApp.getFileById(xlsId); var xlsBlob = xlsFile.getBlob(); var xlsFilename = xlsFile.getName(); var destFolders = []; var wb = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders); return wb;
} function convertExcel2Sheets(excelFile, filename, arrParents) {
var parents = arrParents || []; // check if optional arrParents argument was provided, default to empty array if not if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not
// Parameters for Drive API Simple Upload request (see https://developers.google.com/drive/web/manage-uploads#simple) var uploadParams = { method:'post', contentType: 'application/vnd.ms-excel', // works for both .xls and .xlsx files contentLength: excelFile.getBytes().length, headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}, payload: excelFile.getBytes() };
// Upload file to Drive root folder and convert to Sheets var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);
// Parse upload&convert response data (need this to be able to get id of converted sheet) var fileDataResponse = JSON.parse(uploadResponse.getContentText());
// Create payload (body) data for updating converted file's name and parent folder(s) var payloadData = { title: filename, parents: [] }; if ( parents.length ) { // Add provided parent folder(s) id(s) to payloadData, if any for ( var i=0; i<parents.length; i++ ) { try { var folder = DriveApp.getFolderById(parents[i]); // check that this folder id exists in drive and user can write to it payloadData.parents.push({id: parents[i]}); } catch(e){} // fail silently if no such folder id exists in Drive } } // Parameters for Drive API File Update request (see https://developers.google.com/drive/v2/reference/files/update) var updateParams = { method:'put', headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}, contentType: 'application/json', payload: JSON.stringify(payloadData) };
// Update metadata (filename and parent folder(s)) of converted sheet UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);
Можно файл excel на гугл диске конвертировать в гугл таблицу, прочитать его и потом грохнуть. Есть такой скрипт для конвертации в инете.Рабочий, проверил
[vba]
Код
function testConvertExcel2Sheets() { var xlsId = "0B3........lU"; // ID Excel файла на гугл диске var xlsFile = DriveApp.getFileById(xlsId); var xlsBlob = xlsFile.getBlob(); var xlsFilename = xlsFile.getName(); var destFolders = []; var wb = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders); return wb;
} function convertExcel2Sheets(excelFile, filename, arrParents) {
var parents = arrParents || []; // check if optional arrParents argument was provided, default to empty array if not if ( !parents.isArray ) parents = []; // make sure parents is an array, reset to empty array if not
// Parameters for Drive API Simple Upload request (see https://developers.google.com/drive/web/manage-uploads#simple) var uploadParams = { method:'post', contentType: 'application/vnd.ms-excel', // works for both .xls and .xlsx files contentLength: excelFile.getBytes().length, headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}, payload: excelFile.getBytes() };
// Upload file to Drive root folder and convert to Sheets var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);
// Parse upload&convert response data (need this to be able to get id of converted sheet) var fileDataResponse = JSON.parse(uploadResponse.getContentText());
// Create payload (body) data for updating converted file's name and parent folder(s) var payloadData = { title: filename, parents: [] }; if ( parents.length ) { // Add provided parent folder(s) id(s) to payloadData, if any for ( var i=0; i<parents.length; i++ ) { try { var folder = DriveApp.getFolderById(parents[i]); // check that this folder id exists in drive and user can write to it payloadData.parents.push({id: parents[i]}); } catch(e){} // fail silently if no such folder id exists in Drive } } // Parameters for Drive API File Update request (see https://developers.google.com/drive/v2/reference/files/update) var updateParams = { method:'put', headers: {'Authorization': 'Bearer ' + ScriptApp.getOAuthToken()}, contentType: 'application/json', payload: JSON.stringify(payloadData) };
// Update metadata (filename and parent folder(s)) of converted sheet UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);
doober, конвертирует, но ложит просто на диск. Можно ли его допилить, чтобы клал в определённую папку? И при обновлении удалял старую версию, иначе при конвертации получаются дублирующие файл "название (1)" и так далее
doober, конвертирует, но ложит просто на диск. Можно ли его допилить, чтобы клал в определённую папку? И при обновлении удалял старую версию, иначе при конвертации получаются дублирующие файл "название (1)" и так далееrexar