Перекрестные ссылки на файлы Excel с помощью Power Automate
В этом решении показано, как сравнивать данные в двух файлах Excel для поиска несоответствий. Он использует сценарии Office для анализа данных и Power Automate для обмена данными между книгами.
Этот пример передает данные между книгами с помощью объектов JSON . Дополнительные сведения о работе с JSON см. в статье Использование JSON для передачи данных в скрипты Office и из нее.
Пример сценария
Вы являетесь координатором мероприятий, который запланирует докладчиков для предстоящих конференций. Данные о событиях хранятся в одной электронной таблице, а регистрация говорящего — в другой. Чтобы обеспечить синхронизацию двух книг, используйте поток со сценариями Office, чтобы выделить любые потенциальные проблемы.
Примеры файлов Excel
Скачайте следующие файлы, чтобы получить готовые к использованию книги для примера.
Добавьте следующие скрипты, чтобы попробовать пример самостоятельно! В Excel используйте команду Автоматизировать>новый скрипт , чтобы вставить код и сохранить скрипты с предложенными именами.
Пример кода: получение данных о событиях
function main(workbook: ExcelScript.Workbook): string {
// Get the first table in the "Keys" worksheet.
let table = workbook.getWorksheet('Keys').getTables()[0];
// Get the rows in the event table.
let range = table.getRangeBetweenHeaderAndTotal();
let rows = range.getValues();
// Save each row as an EventData object. This lets them be passed through Power Automate.
let records: EventData[] = [];
for (let row of rows) {
let [eventId, date, location, capacity] = row;
records.push({
eventId: eventId as string,
date: date as number,
location: location as string,
capacity: capacity as number
})
}
// Log the event data to the console and return it for a flow.
let stringResult = JSON.stringify(records);
console.log(stringResult);
return stringResult;
}
// An interface representing a row of event data.
interface EventData {
eventId: string
date: number
location: string
capacity: number
}
Пример кода: проверка регистрации говорящего
function main(workbook: ExcelScript.Workbook, keys: string): string {
// Get the first table in the "Transactions" worksheet.
let table = workbook.getWorksheet('Transactions').getTables()[0];
// Clear the existing formatting in the table.
let range = table.getRangeBetweenHeaderAndTotal();
range.clear(ExcelScript.ClearApplyTo.formats);
// Compare the data in the table to the keys passed into the script.
let keysObject = JSON.parse(keys) as EventData[];
let speakerSlotsRemaining = keysObject.map(value => value.capacity);
let overallMatch = true;
// Iterate over every row looking for differences from the other worksheet.
let rows = range.getValues();
for (let i = 0; i < rows.length; i++) {
let row = rows[i];
let [eventId, date, location, capacity] = row;
let match = false;
// Look at each key provided for a matching Event ID.
for (let keyIndex = 0; keyIndex < keysObject.length; keyIndex++) {
let event = keysObject[keyIndex];
if (event.eventId === eventId) {
match = true;
speakerSlotsRemaining[keyIndex]--;
// If there's a match on the event ID, look for things that don't match and highlight them.
if (event.date !== date) {
overallMatch = false;
range.getCell(i, 1).getFormat()
.getFill()
.setColor("FFFF00");
}
if (event.location !== location) {
overallMatch = false;
range.getCell(i, 2).getFormat()
.getFill()
.setColor("FFFF00");
}
break;
}
}
// If no matching Event ID is found, highlight the Event ID's cell.
if (!match) {
overallMatch = false;
range.getCell(i, 0).getFormat()
.getFill()
.setColor("FFFF00");
}
}
// Choose a message to send to the user.
let returnString = "All the data is in the right order.";
if (overallMatch === false) {
returnString = "Mismatch found. Data requires your review.";
} else if (speakerSlotsRemaining.find(remaining => remaining < 0)){
returnString = "Event potentially overbooked. Please review."
}
console.log("Returning: " + returnString);
return returnString;
}
// An interface representing a row of event data.
interface EventData {
eventId: string
date: number
location: string
capacity: number
}
Поток Power Automate: проверка несоответствий в книгах
Этот поток извлекает сведения о событиях из первой книги и использует эти данные для проверки второй книги.
Войдите в Power Automate и создайте мгновенный облачный поток.
Выберите Вручную активировать поток и нажмите кнопку Создать.
В построителе потоков нажмите кнопку + и добавьте действие. Выберите действие Выполнить скрипт соединителя Excel Online (бизнес). Используйте следующие значения для действия.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- Файл: event-data.xlsx (выбран с помощью выбора файлов)
- Скрипт: получение данных о событиях
Переименуйте этот шаг. Выберите текущее имя "Выполнить скрипт" в области задач и измените его на "Получить данные о событиях".
Добавьте второе действие, использующее действие запуска скрипта соединителя Excel Online (бизнес). Это действие использует возвращаемые значения из скрипта получения данных события в качестве входных данных для сценария проверки данных события . Используйте следующие значения для действия.
- Расположение: OneDrive для бизнеса
- Библиотека документов: OneDrive
- Файл: speaker-registration.xlsx (выбран с помощью выбора файлов)
- Сценарий: проверка регистрации говорящего
- ключи: результат (динамическое содержимое из получения данных о событиях)
Переименуйте этот шаг. Выберите текущее имя "Запуск скрипта 1" в области задач и измените его на "Проверить регистрацию говорящего".
В этом примере в качестве почтового клиента используется Outlook. В этом примере добавьте действие отправки и электронной почты (V2)соединителя Outlook Office 365. Вы можете использовать любой соединитель электронной почты, поддерживаемый Power Automate. В этом действии в качестве основного содержимого сообщения электронной почты используются возвращаемые значения из скрипта проверки регистрации говорящего . Используйте следующие значения для действия.
- По адресу: ваша тестовая учетная запись электронной почты (или личная электронная почта)
- Тема: результаты проверки событий
- Текст: результат (динамическое содержимое из раздела Проверка регистрации говорящего)
Сохраните поток. Конструктор потоков должен выглядеть так, как показано на следующем рисунке.
Нажмите кнопку Тестировать на странице редактора потоков или запустите поток через вкладку Мои потоки . Обязательно разрешите доступ при появлении запроса.
Вы должны получить электронное письмо с сообщением "Найдено несоответствие. Данные требуют проверки". Это указывает на различия между строками в speaker-registrations.xlsx и строками в event-data.xlsx. Откройте speaker-registrations.xlsx , чтобы увидеть несколько выделенных ячеек, в которых есть потенциальные проблемы с описаниями регистрации говорящего.
Office Scripts