Пример работы с подсказками DaData в Google Taблицах

README.md

Как подключить скрипт к Гугл-таблице:

1. Открыть таблицу.
2. В меню выбрать Расширения > Apps Script
3. Вставить код из файла suggest-google-sheets.js
4. Вместо ВАШ_API_КЛЮЧ указать API-ключ из личного кабинета (https://dadata.ru/profile/#info)
5. Сохранить.
6. В тулбаре выбрать функцию guessParty и нажать на кнопку Выполнить.
7. Гугл запросит «разрешения на доступ к вашим данным». Нажать «проверить разрешения», выбрать ваш гугл-аккаунт.
8. Если появится окно «Эксперты Google не проверяли это приложение» — нажать на «Дополнительные настройки», затем «Перейти на страницу... (небезопасно)».
9. Нажать на «Разрешить».
10. Убедиться, что в журнале выполнения написано «Выполнение завершено» и нет ошибок.
11. Вернуться к таблице.
12. Указать в ячейке ИНН компании. Например, в ячейке A1 указать 7719402047.
13. В другой ячейке вызвать функцию guessParty. Например, в ячейчке A2 указать =guessParty(A1)
14. Увидите результат: название, ОГРН, ИНН, КПП, ОКВЭД и адрес компании.

suggest-google-sheets.js

// Замените на свой API-ключ из личного кабинета (https://dadata.ru/profile/#info)
var API_KEY = "ВАШ_API_КЛЮЧ";

/***
/* Не меняйте код ниже этой строчки
 ***/ 

var FORMATTERS = {
  party: formatParty,
  bank: formatBank
}

function formatParty(suggestion) {
  var party = suggestion.data;
  return [
      ["Название", party.name.short_with_opf],
      ["ОГРН", party.ogrn],
      ["ИНН", party.inn],
      ["КПП", party.kpp],
      ["ОКВЭД", party.okved],
      ["Дата регистрации", party.state.registration_date],
      ["Адрес", party.address.value]
    ];
}

function formatBank(suggestion) {
  var bank = suggestion.data;
  return [
      ["Название", bank.name.payment],
      ["БИК", bank.bic],
      ["SWIFT", bank.swift],
      ["Адрес", bank.address.value]
    ];
}

function guessParty(query) {
  return guess("party", query);
}

function guessBank(query) {
  return guess("bank", query);
}

function guess(type, query) {
  if (API_KEY === "ВАШ_API_КЛЮЧ") {
    return "Укажите ваш API-ключ в переменной API_KEY";
  }
  var response = suggest(type, query);
  if (response.suggestions.length > 0) {
    return FORMATTERS[type](response.suggestions[0]);
  } else {
    return "Ничего не найдено";
  }
}

function suggest(type, query) {
  var url = "https://suggestions.dadata.ru/suggestions/api/4_1/rs/suggest/" + type;
  var payload = '{"query": "' + query + '"}'

  var headers = {
    "Authorization": "Token " + API_KEY
  };

  var fetchArgs = {
    method: "POST",
    contentType: "application/json",
    payload: payload,
    headers: headers,
    muteHttpExceptions: false
  };
  return JSON.parse(UrlFetchApp.fetch(url, fetchArgs));
}

Добрый день!
Не могли бы по шагам обьяснить(как с VBA excel), как запустить данный скрипт?
Имеется список ИНН в гугл таблице, нужно получить данные
Заранее спасибо

присоединяюсь к предыдущему вопросу...
помогите пожалуйста!

Добрый день! Подскажите, пожалуйста, как работает данный код?

Всё работает. Спасибо за инструкцию. Пришлось повозится 5 минут из-за того что в гугл таблицах сменились описания, но всё понятно)))

Не работает. Скрипт выполняется, пишется "Loading", но информация не появляется.

Проверено работает. Спасибо.

Добрый день! Подскажите, а если я хочу вернуть не данные организации, а просто данные по неточно введенному адресу, например такие: Город, нас.пункт, и их ФИАС-id, что меняется в коде? Попробовал вот эту часть поменять так (не помогло - возвращает пустые значения):

function formatParty(suggestion) {
var party = suggestion.data;
return [
["Название", party.name.short_with_opf],
["Город", party.city],
["Нас.Пункт", party.settlement],
["ФИАС-id Города", party.city_fias_id],
["ФИАС-id нас.пункта", party.settlement_fias_id]
];
}

Подскажите почему может возникать ошибка?
Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets (line 17).

/**
 * Обрабатывает редактирование в Google Sheets и обновляет поля в зависимости от "ИНН" и "БИК".
 * @param {Object} e - Объект события, содержащий информацию о редактировании.
 */
function onEdit(e) {
  // Проверка наличия объекта события и его свойств
  if (!e || !e.range || !e.source) return;

  // Получаем активный лист и диапазон редактирования
  const sheet = e.source.getActiveSheet();
  const range = e.range;

  // Убедимся, что редактирование произошло на листе "ИМЯ_ВАШЕГО_ЛИСТА" и не в строке заголовков (строка 2)
  if (sheet.getName() !== "ИМЯ_ВАШЕГО_ЛИСТА" || range.getRow() <= 2) return; // Имя рабочего личта ЗАМЕНИТЬЕ НА СВОИ ЗНАЧЕНИЯ

  // Определяем необходимые заголовки для проверки
  const REQUIRED_HEADERS = [
    "ИНН", "Контрагент", "ФИО", "Лицо", "Адрес", "ОКПО", "КПП", "ОГРН", "БИК", "Банк", "ID", "К/C"
  ];

  // Получаем заголовки из второй строки и проверяем наличие всех необходимых заголовков
  const headers = sheet.getRange(2, 1, 1, sheet.getLastColumn()).getValues()[0];
  const missingHeaders = REQUIRED_HEADERS.filter(header => !headers.includes(header));
  
  // Если какие-либо заголовки отсутствуют, отображаем сообщение об ошибке в ячейке "A1"
  if (missingHeaders.length > 0) {
    sheet.getRange("A1").setValue(`Ошибка: отсутствуют заголовки: ${missingHeaders.join(", ")}`);
    return;
  } else {
    // Если все заголовки присутствуют, очищаем сообщение об ошибке в ячейке "A1"
    sheet.getRange("A1").clearContent();
  }

  // Создаем объект с индексами колонок на основе заголовков
  const colIndexes = REQUIRED_HEADERS.reduce((acc, header) => {
    acc[header] = headers.indexOf(header) + 1; // Индекс колонки начинается с 1
    return acc;
  }, {});

  // Логируем индексы колонок для отладки
  Logger.log(`Индексы колонок: ${JSON.stringify(colIndexes)}`);

  // Получаем количество строк в диапазоне редактирования
  const numRows = range.getNumRows();
  
  // Опции для запроса к API
  const apiOptions = {
    method: 'post', // Метод POST
    headers: {
      'Content-Type': 'application/json', // Заголовок для JSON
      'Authorization': 'Token ВАШ_API_КЛЮЧ' // Авторизационный токен ЗАМЕНИТЬЕ НА СВОИ ЗНАЧЕНИЯ
    }
  };

  // Обрабатываем каждую строку в диапазоне редактирования
  for (let i = 0; i < numRows; i++) {
    const row = range.getRow() + i; // Определяем номер строки
    const inn = sheet.getRange(row, colIndexes["ИНН"]).getValue(); // Получаем значение ИНН
    const bik = sheet.getRange(row, colIndexes["БИК"]).getValue(); // Получаем значение БИК

    // Если ИНН пустой и редактирование произошло в колонке ИНН, очищаем связанные поля
    if (!inn && range.getColumn() === colIndexes["ИНН"]) {
      clearRowData(sheet, row, colIndexes, ["Контрагент", "ФИО", "Лицо", "Адрес", "ОКПО", "КПП", "ОГРН", "ID"]);
    } else if (inn) {
      // Если ИНН присутствует, обрабатываем его
      processINN(sheet, row, colIndexes, inn, apiOptions);
    }

    // Если БИК пустой и редактирование произошло в колонке БИК, очищаем связанные поля
    if (!bik && range.getColumn() === colIndexes["БИК"]) {
      clearRowData(sheet, row, colIndexes, ["Банк", "К/C"]);
    } else if (bik) {
      // Если БИК присутствует, обрабатываем его
      processBIK(sheet, row, colIndexes, bik, apiOptions);
    }
  }
}

/**
 * Очищает указанные колонки в заданной строке.
 * @param {Sheet} sheet - Активный лист.
 * @param {number} row - Номер строки, которую нужно очистить.
 * @param {Object} colIndexes - Сопоставление заголовков колонок с индексами.
 * @param {Array<string>} columnsToClear - Список имен заголовков для очистки.
 */
function clearRowData(sheet, row, colIndexes, columnsToClear) {
  // Перебираем каждый заголовок из списка и очищаем соответствующую колонку в строке
  columnsToClear.forEach(column => {
    const colIndex = colIndexes[column]; // Получаем индекс колонки по имени заголовка
    if (colIndex) {
      sheet.getRange(row, colIndex).clearContent(); // Очищаем содержимое ячейки
    }
  });
}

/**
 * Обрабатывает значение ИНН и обновляет соответствующую строку.
 * @param {Sheet} sheet - Активный лист.
 * @param {number} row - Номер строки для обновления.
 * @param {Object} colIndexes - Сопоставление заголовков колонок с индексами.
 * @param {string} inn - Значение ИНН для обработки.
 * @param {Object} apiOptions - Опции для запроса к API.
 */
function processINN(sheet, row, colIndexes, inn, apiOptions) {
  try {
    // Выполняем запрос к API для получения данных по ИНН
    const response = UrlFetchApp.fetch('https://suggestions.dadata.ru/suggestions/api/4_1/rs/findById/party', {
      ...apiOptions,
      payload: JSON.stringify({ query: inn }) // Отправляем ИНН в теле запроса
    });
    const result = JSON.parse(response.getContentText()).suggestions[0]; // Разбираем ответ

    // Извлекаем данные из ответа API или устанавливаем значения по умолчанию
    const values = result ? {
      "Контрагент": result.value || 'Не найдено',
      "ФИО": result.data.type === 'INDIVIDUAL' ? result.data.name.full : (result.data.management ? result.data.management.name : 'Не найдено'),
      "Лицо": result.data.type === 'INDIVIDUAL' ? 'Физ' : 'Юр',
      "Адрес": result.data.address.unrestricted_value || 'Не найдено',
      "ОКПО": result.data.okpo || 'Не найдено',
      "КПП": result.data.kpp || 'Не найдено',
      "ОГРН": result.data.ogrn || 'Не найдено'
    } : {
      "Контрагент": 'Не найдено',
      "ФИО": 'Не найдено',
      "Лицо": 'Не найдено',
      "Адрес": 'Не найдено',
      "ОКПО": 'Не найдено',
      "КПП": 'Не найдено',
      "ОГРН": 'Не найдено'
    };

    // Устанавливаем значения в соответствующие колонки
    Object.entries(values).forEach(([header, value]) => {
      sheet.getRange(row, colIndexes[header]).setValue(value);
    });

    // Определяем последний номер строки и генерируем новый ID
    const lastRow = sheet.getLastRow();
    const id = lastRow > 2 ? Math.max(...sheet.getRange(3, colIndexes["ID"], lastRow - 2, 1).getValues().flat()) + 1 : 1;
    sheet.getRange(row, colIndexes["ID"]).setValue(id);
    
  } catch (error) {
    // Логируем ошибку в случае сбоя запроса
    Logger.log(`Ошибка запроса ИНН: ${error.message}`);
  }
}

/**
 * Обрабатывает значение БИК и обновляет соответствующую строку.
 * @param {Sheet} sheet - Активный лист.
 * @param {number} row - Номер строки для обновления.
 * @param {Object} colIndexes - Сопоставление заголовков колонок с индексами.
 * @param {string} bik - Значение БИК для обработки.
 * @param {Object} apiOptions - Опции для запроса к API.
 */
function processBIK(sheet, row, colIndexes, bik, apiOptions) {
  try {
    // Выполняем запрос к API для получения данных по БИК
    const response = UrlFetchApp.fetch('https://suggestions.dadata.ru/suggestions/api/4_1/rs/findById/bank', {
      ...apiOptions,
      payload: JSON.stringify({ query: bik }) // Отправляем БИК в теле запроса
    });
    const result = JSON.parse(response.getContentText()).suggestions[0]; // Разбираем ответ

    // Извлекаем данные из ответа API или устанавливаем значения по умолчанию
    const bankName = result ? result.value || 'Не найдено' : 'Не найдено';
    const correspondentAccount = result ? result.data.correspondent_account || 'Не найдено' : 'Не найдено';

    // Устанавливаем значения в соответствующие колонки
    sheet.getRange(row, colIndexes["Банк"]).setValue(bankName);
    sheet.getRange(row, colIndexes["К/C"]).setValue(correspondentAccount);
    
  } catch (error) {
    // Логируем ошибку в случае сбоя запроса
    Logger.log(`Ошибка запроса БИК: ${error.message}`);
  }
}