RxJS оператор waitUntil

Общие замечания:

Итак, чтобы понять, что я написал в комментариях, вот контур.

Вы не хотите использовать пользовательскую функцию. Это будет продолжаться при повторных вызовах. Вы определенно рискуете попасть в лимит вызовов API без ключа API и, возможно, с; он неэффективен и не нужен. Вместо этого напишите подраздел, который вы вызываете один раз, и который перебирает все необходимые ячейки на листе и выдает вызовы API и возвращает почтовые индексы. Ключ API - это метод аутентификации, используемый со многими вызовами API. Вы не должны делиться им между прочим.

Эти повторные вызовы, возможно, превышают лимит и тот факт, что UDF часто вычисляются, возможно, источником ваших проблем.

С учетом эффективности сначала удалите дубликаты с листа, чтобы избежать вызовов, которые не требуются. Переключатель обновления экрана и все остальное, например. CalculationMode к руководству во время выполнения.

Из того, что я прочитал, вам нужен ключ API, как только вы нажмете дневной лимит. Не уверен, что ограничение API для бесплатной версии или без ключа API.

Контурный код (XML-запрос с некоторым кодом psuedo):

  Option Explicit
    Public Sub ListZipCodes()
        Dim lat As Double, longitude As Double
        Const APIKEY As String = "yourAPIkey"
        Application.ScreenUpdating = False           '<==Speed up code when actually working with sheet
        'Code to remove duplicates
        'Code to loop sheet and call function on each input set of values

        'Example call. These would be picked up from cells
        lat = 40.714224
        longitude = -73.961452

        Debug.Print GetZipCode(lat, longitude, APIKEY)

        Application.ScreenUpdating = True
    End Sub

    Public Function GetZipCode(ByVal lat As Double, ByVal longitude As Double, ByVal APIKEY As String) As String
        Dim sResponse As String
        With CreateObject("MSXML2.XMLHTTP")
            Dim URL As String
            URL = "https://maps.googleapis.com/maps/api/geocode/xml?latlng=" & lat & "," & longitude & "&key=" & APIKEY
            .Open "GET", URL, False
            .send
            If .Status <> 200 Then 
                GetZipCode = "API call failed"
                Exit Function
            End If
            Dim XMLDoc As New DOMDocument60, XMLNODE As IXMLDOMNode

            XMLDoc.Load .responseBody
            If Len(XMLDoc.Text) = 0 Then GetZipCode = "No data"

            Set XMLNODE = XMLDoc.SelectSingleNode("/GeocodeResponse/result/formatted_address")
            GetZipCode = Split(Trim$(Split(XMLNODE.Text, Chr$(44))(2)), Chr$(32))(1)
        End With
    End Function

Запрос JSON, а не XML-ответ:

Причина, вызвавшая отказ JSON, заключалась в том, что ответ нужно было декодировать. Вот функция, переписанная для обработки ответа JSON.

Для этого требуется загрузка JSONConverter , которую вы затем импортируете и добавляете ссылку на Microsoft Scripting Runtime через VBE> Инструменты > Ссылки

Пример ниже выполнялся с

latitude: 42.9865913391113, 
longitude: -100.137954711914

VBA:

Public Function GetZipCode(ByVal lat As Double, ByVal longitude As Double, ByVal APIKEY As String) As String
    Dim sResponse As String, json As Object
    With CreateObject("MSXML2.XMLHTTP")
        Dim URL As String, formattedAddress As String
        URL = "https://maps.googleapis.com/maps/api/geocode/json?latlng=" & lat & "," & longitude & "&key=" & APIKEY
        .Open "GET", URL, False
        .send
        If .Status <> 200 Then 
            GetZipCode = "API call failed"
            Exit Function
        End If
        Set json = JsonConverter.ParseJson(StrConv(.responseBody, vbUnicode))

        formattedAddress = json("results").item(1)("formatted_address")
        GetZipCode = Split(Trim$(Split(formattedAddress, Chr$(44))(2)), Chr$(32))(1)
    End With
End Function

С запросом JSON исходный объект, который вы возвращаете, является словарь, обозначенный открытием "{" в декодированном отклике:

т.е. Set json = JsonConverter.ParseJson(StrConv(.responseBody, vbUnicode)) возвращает объект словаря

. Интересующие данные в словаре имеют ключ "results", как вы можете заметить из вышеизложенного.

Доступ к нему можно получить с помощью json("results"), который возвращает набор словарей. Это обозначается следующим "[", для сбора, а затем и началом первого словаря в коллекции, снова обозначенным "{".

Я могу захватить первый словарь в коллекции по индексу с помощью:

json("results").item(1)

Проверка ключей в этом словаре показывает, что один из ключей - это то, что мы имеем после того, как "formatted_address" ,

Связанное значение является примитивным типом данных; в этом случае строка. Это означает, что мы можем напрямую обращаться к нему с помощью ключа (еще один объект не возвращается).

formattedAddress = json("results").item(1)("formatted_address")

Теперь, когда у нас есть адресная строка, мы можем проанализировать ее, как это было раньше:

GetZipCode = Split(Trim$(Split(formattedAddress, Chr$(44))(2)), Chr$(32))(1)

Конечная нота:

Вы можете использовать Postman среди других инструментов для проверки вызовов API и в этом случае проверить ответ JSON. Действительно, чтобы узнать, какой ответ вы полностью прекратите.

Справка:

Очень быстро и легко настроить проект, сгенерировать ключ API и начать. Возможно, 10 минут для чтения и выполнения.

1. Инструкции по настройке проекта и получению ключа API
2. Включение API
3. Понимание того, как сделать API-вызовы API геокодирования