Как написать скрипт для google sheets с автозаполнением?

Question

[dimabah]

Добрый день.

Я создаю сценарий с помощью сервиса Make. Я получаю от чат-бота данные и автоматически вношу их в созданную строку в таблице Google Sheets. Однако, мне нужно для этих данных использовать две формулы. Я прописывал их вручную, но Make их очищает когда вносит данные. Мне посоветовали использовать скрипт в Google Sheets.

Какие данные получаю от бота:
Ячейка А - пол - man/woman
Ячейка В - вес - число
Ячейка С - рост - число
Ячейка D - возраст - число

Теперь из этих данных мне нужно получить результаты:
Ячейка E - дневную норму каллорий (формулу использовал =ARRAYFORMULA(IF(A2:A = "man", 88.362 + (13.397 * B2:B) + (4.799 * C2:C) - (5.677 * D2:D), IF(A2:A = "woman", 447.593 + (9.247 * B2:B) + (3.098 * C2:C) - (4.330 * D2:D), "Неверный пол"))))
Ячейка F - выбор дефицита (формулу использовал =ARRAYFORMULA(IF(E2:E > 2300, "1800 ккал", IF(E2:E > 2000, "1650 ккал", IF(E2:E > 1700, "1500 ккал", "1350 ккал"))))

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

Comments

[dim5x]

Чат-бот не может подсчитывать их сам и вносить уже готовую строку?

Answer 1

[Bandyloo]

Ответ chatgpt, проверяйте:

Чтобы решить проблему, можно использовать Google Apps Script. Скрипт будет автоматически заполнять формулы в ячейки после добавления данных в таблицу. Вот как это сделать:

1. Откройте Google Sheets.

2. Перейдите в меню Extensions → Apps Script.

3. Вставьте следующий скрипт:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;

// Убедитесь, что скрипт работает только для определённого листа
if (sheet.getName() === "Sheet1") { // Замените "Sheet1" на имя вашего листа
var row = range.getRow();
var col = range.getColumn();

// Проверяем, что данные добавляются в диапазон A:D
if (col >= 1 && col <= 4 && row > 1) { // Пропускаем заголовок
var gender = sheet.getRange(row, 1).getValue(); // Ячейка A
var weight = sheet.getRange(row, 2).getValue(); // Ячейка B
var height = sheet.getRange(row, 3).getValue(); // Ячейка C
var age = sheet.getRange(row, 4).getValue(); // Ячейка D

// Вычисление дневной нормы калорий
var calories;
if (gender === "man") {
calories = 88.362 + (13.397 * weight) + (4.799 * height) - (5.677 * age);
} else if (gender === "woman") {
calories = 447.593 + (9.247 * weight) + (3.098 * height) - (4.330 * age);
} else {
calories = "Неверный пол";
}
sheet.getRange(row, 5).setValue(calories); // Записываем в ячейку E

// Вычисление дефицита калорий
var deficit;
if (typeof calories === "number") {
if (calories > 2300) {
deficit = "1800 ккал";
} else if (calories > 2000) {
deficit = "1650 ккал";
} else if (calories > 1700) {
deficit = "1500 ккал";
} else {
deficit = "1350 ккал";
}
} else {
deficit = "Ошибка";
}
sheet.getRange(row, 6).setValue(deficit); // Записываем в ячейку F
}
}
}

4. Сохраните проект, например, как AutoFillFormulas.

5. Перейдите в Triggers (триггеры) и добавьте новый триггер:

Выберите функцию onEdit.

Выберите событие On edit.

Теперь, когда Make добавляет данные в столбцы A:D, скрипт автоматически вычислит значения для столбцов E и F.