VBA - SQL Server - GoogleCalendar
В этой статье мы решили поделиться своим опытом программного взаимодействия с GoogleCalendar: ввод событий, синхронизация, сверка состояний. Наша компания активно использует GoogleCalendar, так как проведение различного рода семинаров для наших клиентов является одним из видов деятельности. Мы ведем порядка 6 календарей по территориям, где проводятся занятия и 12 календарей для лекторов, которые являются дублирующими. Можно отметить стабильный уровень работы GoogleCalendar, поэтому данное мероприятие можно считать целесообразным. Выбор VBA для такой работы является своеобразной гарантией простоты освоения процесса. Данная статья не является подробной инструкцией, так как технический материал (собственно, основной код) недостаточно представлен, но его можно расширить, если он будет востребован. Перед тем, как начать работать в этом направлении, необходимо иметь хотя бы общие представления о том, что такое REST API, запросы к серверу (они же запросы HTTP), код JSON и настроиться, что ничего сложного здесь нет. И так, поехали.
Всё, что вы будете делать с календарем (заполнять его событиями, удалять их, обновлять) делается объектами, которые являются рабочими лошадками, и которыми вы будете управлять запросами HTTP к гугловскому REST API по определенным адресам, используя предоставленные вам многочисленные идентификаторы. В нашем случае достаточно только одного объекта-коллекции - Events с его свойствами и методами, но, тем, не менее, придется сделать всё по полной программе.
1. Необходимо создать свой аккаунт в Google. Скорее всего, он уже у вас есть, поэтому войдите в него.
2. Теперь надо создать проект API. Программно обращаясь к нему, вы будете производить все действия с календарем. Вот ссылка, где вы сможете это сделать. https://console.developers.google.com/flows/enableapi?apiid=calendar
Если войти по ссылке без предватительного входа в свой аккаунт, то все равно придется в него входить позже.
3. Теперь надо создать учетные данные. При создании, укажите, что доступ к API будет выполняться из приложения (не из браузера). После создания учетных данных, лучше скачать строку JSON с ними к себе на РС и занести в таблицу SQL или Access, назовем её таблица идентификаторов:
4. Теперь необходимо создать календарь или календари, объявить их общедоступными, правильно настроить (отображение времени, цвет и прочее). Не нужно пользоваться уже созданным, при создании аккаунта календарем, работа с ним имеет неприятные нюансы, создавайте свои. Идентификатор каждого календаря надо занести в таблицу - таблицу календарей. Не стоит идентификаторы и календари держать в одной таблице. Если идентификаторы вы будете просто считывать в форму, то по календарям, если их несколько, надо будет делать цикл. Если у вас один календарь, то скоро появится ещё и ещё. Пример нашей таблицы календарей:
Если вы всё сделали правильно и у вас имеются 2 таблицы с необходимыми идентификаторами (см. как у нас), то можно приступать к самой интересной части задачи. Важный момент: если вы работаете через прокси-сервер, необходимо будет указывать его параметр во всех HTTP-запросах. Если его параметр обновляется через системный реестр, то занесите в таблицу идентификаторов путь, по которому вы будете его считывать. Можно еще брать из браузера. Проконсультируйтесь со своим сисадмином по этому вопросу, так как он принципиальный. И еще, не пытайтесь скопировать наши личные идентификаторы, они вам не помогут. А вот пути, скорее всего, будут те же.
5. Теперь самый ответственный момент - аутентификация.
5.1. Получение кода первичной аутентификации (код аутентификации). Не путать с токеном, о нём позже. Этот код нужен для последующего получения токенов. Данная процедура делается очень редко, но с неё всё начинается.
Эту процедуру можно сделать как угодно, как вам удобно. Например, вручную запустить Explorer по определенному адресу с нужными идентификаторами, получить код аутентификации, затем получать все остальное.
У нас эта процедура сделана в составе кода. Сначала создается объект Explorer. Прикрепляются к нему необходимые идентификаторы (см. таблицу) и всё это запускаетсяпо пути:
'Адрес для аутентификации
strUrlAuth = strUrlAuth & "?response_type=code"
strUrlAuth = strUrlAuth & "&client_id=" & strClientID
strUrlAuth = strUrlAuth & "&redirect_uri=" & strRedirectUri
strUrlAuth = strUrlAuth & "&scope=" & strScope
Set IE = New InternetExplorer
IE.Navigate strUrlAuth
IE.visible = True
Do While IE.Busy Or IE.ReadyState <> 4 'READYSTATE_COMPLETE
DoEvents
Loop
который приведет к своему аккаунту. Далее в программе создаем ожидание, например, 1 минуту, чтобы успеть войти в свой аккаунт. Входим в аккаунт, далее на страницу кода, видим код в поле. Там будет рекомендация скопировать его через буфер обмена, но в нашем случае это не актуально, потому что получаем ответ в виде строки JSON, где он есть. Далее программно работаем с текстом, который в JSON, используя массив и функции Split, Mid, Left, InStr, вычленяем из текста длинный такой код и присваиваем его строковой переменной. Текст, который в JSON, представляет собой обычный текст, основным достоинством которого является, то, что в конце каждой строки есть символ перехода на другую строку - vblf или Chr(10). Поэтому удобно Split-ом его построчно резать и засовывать в массив.
5.2. По коду аутентификации получаем ответ JSON, который содержит токен, токен-рефреш, время жизни токена, тип токена, прочее,
Dim objXMLRequest As MSXML2.ServerXMLHTTP60 'именно этот класс, другие могут глючить
With objXMLRequest
strRequest = "code=" & strAuthCode
strRequest = strRequest & "&client_id=" & strClientID
strRequest = strRequest & "&client_secret=" & strClientSecret
strRequest = strRequest & "&redirect_uri=" & strRedirectUri
strRequest = strRequest & "&grant_type=authorization_code"
'Прокси (есть или нет)
If Len(Nz(WshShell.RegRead(strProxy), "")) > 0 Then .SetProxy 2, WshShell.RegRead(strProxy)
.Open "POST", strUrlToken, False
.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send strRequest
If .Status <> 200 Then
MsgBox "Ошибка получения токена!" & vbCrLf & vbCrLf & .Status & ": " & .responseText
End If
'Разбираем строку ответа
arrResponce = Split(.responseText, vbLf)
далее, так же разбираем ответ из массива вышеуказанными функциями и заносим всё в таблицу токенов.
Токен выдается только на 1 час, дальнейшее его получение осуществляется по токену-рефрешу, который всегда актуален. Итак, аутентификация пройдена, можно заполнять календарь, что делается POST-запросами к серверу. Особое значение имеют идентификаторы событий, которые должны быть уникальными и могут представлять собой: буквы a-z нижний регистр и все цифры, от 5 до 1024 символов. У нас только цифры, длиной от 5 до 7 символов.
Представленные выше и ниже фрагменты кода, естественно с условностями. Необходимо присоединить библиотеки, объявить переменные, объекты, массивы, а по завершению всего подчистить за собой, сделать обработку ошибок, создать функции, причем универсальные, например, проверки токена на свежесть, получения нового токена по токену-рефрешу, функцию для ввода событий (POST-запрос), функцию синхронизации (обновления) событий (PUT-запрос, она же отменяет событие, удаляет и прочее, адресуясь непосредственно к событию, а не только к календарю) и много еще чего. Естественно, что вы будете выбирать данные из своей базы данных, поэтому придется делать запросы в составе хранимой процедуры, открывать там курсоры, организовывать циклы по ним и формировать, таким образом, строку JSON. Но это все уже обычная, понятная работа, а здесь только основные моменты, касающиеся именно календаря.
Опять же, всё это вопрос творчества программиста. Можно делать такие запросы, как угодно. Например, можно сделать по событиям (ID события). Для события формируется на сервере строка JSON, затем идет на клиент, затем в Google. Анализируется ответ, если все в порядке, то следующая итерация. Если сбой, то - в таблицу ошибок и т.д.
У нас сделано по-другому. На сервере формируется сразу массив информации по дата-время начала, дата-время конца события и ID календаря. После завершения процесса, весь массив Select-ом отправляется на клиент, в рекордсет, делается цикл по нему и POST-запросом по событиям вводится в Google. Параллельно с этим все эти события вводятся в таблицу событий. Вводить одни и те же события в календарь нельзя, будет критический сбой, поэтому такая таблица нужна. В ней же будут отражаться все дальнейшие действия с календарем, и она же очень помогает для анализа информации, которая в календарях, у нас в форме по ней сделаны различные фильтры, сортировки и там же функционал управления календарями.
Здесь есть один нюанс. А если какое-либо событие сбойнуло? Ничего страшного, нужно прогнать весь цикл, пусть введется то, что введется. А далее делается сверка состояний таблицы событий и GoogleCalendar. Это очень простая процедура, которая выводит все несоответствия. Сбои будут только на первом этапе. Основная причина сбоев - это какой-нибудь мерзкий символ, который впоследствии выявляется и Replace-ом убивается. Проникает он в базу через копирование с помощью буфера обмена. В принципе, можно чистить текст перед выгрузкой в процедуру, можно это делать перед вводом его в базу, а можно не париться, и так много всяких наворотов. Очень важно: перед отправкой массива данных на клиент (в рекордсет), надо, еще в процедуре SQL все символы конца строки, табуляции и возврата каретки заменить Replace-ом на пустую строку,
select @CalendarID as CalendarID,
StudyID,
REPLACE(REPLACE(REPLACE(JsonSrting,CHAR(9),''),CHAR(10),''),CHAR(13),'') as JsonSrting
а на клиенте, например, в Access-е, перед отправкой в Google, заменить Replace-ом двойные кавычки на одинарные:
.Send Replace(rst!JsonSrting, """", "'")
Именно в такой последовательности.
Что касается кода по формированию строки JSON, он прост, не смотря на то, что сама хранимая процедура громоздкая и код формируется в нескольких циклах по курсорам. Вот некоторые свойства, которые вы должны передавать для заполнения календаря и, главное, как. Для примера пришлось сократить почти всё, чтобы не перегружать вас.
Set @JsonSrting =
'{
"end": {"dateTime":"' + @StudyFinish + '"}
,"start": {"dateTime":"' + @StudyStart + '"}
,"id":"' + CAST(@StudyID as nvarchar(50)) + '"
,"visibility":"' + @Visibility + '"
,"transparency":"transparent"
,"guestsCanSeeOtherGuests":"False"
,"location":"' + @AddressView + '"
,"summary":"' + @StudyOfficialType + ' ' + @StudyTheme + ' (' + @InstructorJobTitle + ' ' + @Instructor + ')"
,"description":"' + @StudyTheme +
'"}'
Так же в форме по управлению календарями желательно сделать 2 невидимых списка (ListBox), к которым в качестве источника присоединить идентификаторы и календари. Удобно потом считывать из них идентификаторы и делать циклы по календарям.
Что касается POST-запроса по заполнению календаря, то вот заключительный момент:
'Если рекордсет не пустой, то делаем по нему цикл и посылаем запросы в Google
With objXMLRequest
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
'Прокси (есть или нет)
If Len(Nz(WshShell.RegRead(strProxy), "")) > 0 Then .SetProxy 2, WshShell.RegRead(strProxy)
.Open "POST", strURL, False
.SetRequestHeader "Content-Type", "application/json"
.SetRequestHeader "Authorization", "Bearer " & strToken
.SetRequestHeader "Accept-Charset", "UTF-8"
.Send Replace(rst!JsonSrting, """", "'")
'204 - статус при удалении, в остальных случаях - 200
If .Status <> 200 And .Status <> 204 Then MsgBox "Ошибка ввода события в календарь.” & vbCrLf &
vbCrLf & .Status & ": " & .responseText, vbCritical, "Сбой в
календаре ID: " & rst!CalendarID & ", событие ID: " & rst!StudyID
End If
rst.MoveNext
Loop
End If
End With
Ну вот, в общем, и всё. В связи с довольно объемным функционалом, который есть в реальности, а здесь представлены только основные моменты, осталось впечатление некой незаконченности, много чего еще надо бы сказать, но, чтобы сформировать мнение о предстоящей работе и начать, достаточно. В заключение благодарим нашего программиста Алексея Брыкалина за исследовательскую деятельность в этом вопросе.
Желаем всем вам успехов!