Power Query. Веб-запросы. Личный OneDrive — подключаемся напрямую к файлам и папкам

Описание

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

В качестве примера мы подключимся сначала к одному файлу xlsx в личном облаке OneDrive, а потом к папке с несколькими xlsx файлами.

Решение

Для того, чтобы прочитать файл или папку в OneDrive мы воспользуемся функциями Binary.ToText и Text.ToBinary.

Этими функциями мы сможем преобразовать ссылку OneDrive в нужный формат.

Далее мы создадим строку запроса к API OneDrive.

Примененные функции

• Excel.CurrentWorkbook
• Binary.ToText
• Text.ToBinary
• TextEncoding.Utf8
• BinaryEncoding.Base64
• Excel.Workbook
• Web.Contents
• Json.Document
• Table.FromList
• Splitter.SplitByNothing
• ExtraValues.Error
• Table.ExpandRecordColumn
• Table.AddColumn
• Table.SelectColumns
• Table.ExpandTableColumn
• Table.SelectRows

Код

Для начала создадим отдельный запрос с названием url_single с кодом формирования запроса к API. Код для формирования запроса к API для подключения к одному файлу:

let
   // берем url из именной ячейки с листа Excel
   one_drive_url = Excel.CurrentWorkbook(){[Name = "od_file"]}[Content]{0}[Column1],
   // преобразовываем ссылку в формат base64
   base64 = Binary.ToText(
      Text.ToBinary(one_drive_url, TextEncoding.Utf8),
      BinaryEncoding.Base64
   ),
   // готовый запрос API
   new_url = "https://api.onedrive.com/v1.0/shares/u!" & base64 & "/root/content"
in
   new_url

То же самое сделаем и для папки в облаке OneDrive. Создадим запрос с названием url_multiple:

let
   one_drive_url = Excel.CurrentWorkbook(){[Name = "od_folder"]}[Content]{0}[Column1],
   base64 = Binary.ToText(
      Text.ToBinary(one_drive_url, TextEncoding.Utf8),
      BinaryEncoding.Base64
   ),
   new_url = "https://api.onedrive.com/v1.0/shares/u!" & base64 & "/root/children"
in
   new_url

Теперь подключимся к одному файлу на личном облаке OneDrive:

let
  source = Excel.Workbook(Web.Contents(url_single)),
  result = source{[Item = "ККМтовар", Kind = "Table"]}[Data]
in
  result

Подключение к папке будет немного сложнее, потому что нужно будет прочитать JSON. Код будет такой:

let
   source = Json.Document(Web.Contents(url_multiple)),
   value = source[value],
   record_to_table = Table.FromList(
      value,
      Splitter.SplitByNothing(),
      null,
      null,
      ExtraValues.Error
   ),
   expand_table_1 = Table.ExpandRecordColumn(
      record_to_table,
      "Column1",
      {"webUrl"},
      {"webUrl"}
   ),
   col_get_data = Table.AddColumn(
      expand_table_1,
      "GetData",
      each fn_1drive_file_url([webUrl])
   ),
   col_read_xl = Table.AddColumn(
      col_get_data,
      "read_xl",
      each Excel.Workbook(Web.Contents([GetData]))
   ),
   select_cols_1 = Table.SelectColumns(col_read_xl, {"read_xl"}),
   expand_table_2 = Table.ExpandTableColumn(
      select_cols_1,
      "read_xl",
      {"Name", "Data", "Kind"},
      {"Name", "Data", "Kind"}
   ),
   select_rows = Table.SelectRows(expand_table_2, each ([Kind] = "Table")),
   select_cols_2 = Table.SelectColumns(select_rows, {"Data"}),
   combine_tables = Table.ExpandTableColumn(
      select_cols_2,
      "Data",
      {
         "Дата",
         "Месяц",
         "Документ",
         "Вид операции",
         "Тип оплаты",
         "Вид оплаты",
         "POSID",
         "Торговая точка",
         "Регион",
         "Канал продажи",
         "ТМ",
         "Логин WD",
         "Код",
         "Номенклатура",
         "Количество",
         "Себестоимость",
         "Цена",
         "Скидка",
         "Сумма продаж",
         "Валовая прибыль, руб.",
         "Сумма у. е. ФО",
         "Премия",
         "Бонусная ставка"
      },
      {
         "Дата",
         "Месяц",
         "Документ",
         "Вид операции",
         "Тип оплаты",
         "Вид оплаты",
         "POSID",
         "Торговая точка",
         "Регион",
         "Канал продажи",
         "ТМ",
         "Логин WD",
         "Код",
         "Номенклатура",
         "Количество",
         "Себестоимость",
         "Цена",
         "Скидка",
         "Сумма продаж",
         "Валовая прибыль, руб.",
         "Сумма у. е. ФО",
         "Премия",
         "Бонусная ставка"
      }
   )
in
   combine_tables