function GETFOLDERURLBYNAME(folderName) {
try {
// Проверяем авторизацию
if (!isAuthorized()) {
throw new Error("Требуется авторизация. Запустите скрипт через редактор сначала.");
}
var folders = DriveApp.getFoldersByName(folderName);
if (folders.hasNext()) {
var folder = folders.next();
return folder.getUrl();
} else {
return "Папка не найдена";
}
} catch (e) {
return "Ошибка: " + e.message;
}
}
// Функция для проверки авторизации
function isAuthorized() {
try {
DriveApp.getRootFolder();
return true;
} catch (e) {
return false;
}
}
// Запустите эту функцию один раз из редактора для авторизации
function authorizeScript() {
Logger.log("Скрипт авторизован. Теперь можно использовать функцию в таблице.");
DriveApp.getRootFolder();
}/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
if (!e) {
throw new Error(
'Please do not run the onEdit(e) function in the script editor window. '
+ 'It runs automatically when you hand edit the spreadsheet.'
);
}
copyRowWhenCheckboxTicked_(e);
}
/**
* When a checkbox is ticked, copies columns A:F of the active row
* to the sheet named in row 3.
*
* @param {Object} e The onEdit() event object.
*/
function copyRowWhenCheckboxTicked_(e) {
'use strict';
try {
if (e.value !== 'TRUE') {
return;
}
const ss = SpreadsheetApp.getActive();
const sheet = e.range.getSheet();
const values = sheet.getRange(`A${e.range.rowStart}:F${e.range.rowStart}`).getValues().flat();
const targetSheetName = sheet.getRange(3, e.range.columnStart).getValue();
const targetSheet = ss.getSheetByName('Лист2');
if (!targetSheet) {
throw new Error(`Cannot find sheet '${targetSheetName}'.`)
}
targetSheet.appendRow(values);
showMessage_(`Copied row ${e.range.rowStart} to sheet '${targetSheetName}'.`);
} catch (error) {
showAndThrow_(error);
}
}
/**
* Shows error.message in a pop-up and throws the error.
*
* @param {Error} error The error to show and throw.
*/
function showAndThrow_(error) {
// version 1.0, written by --Hyde, 16 April 2020
var stackCodeLines = String(error.stack).match(/\d+:/);
if (stackCodeLines) {
var codeLine = stackCodeLines.join(', ').slice(0, -1);
} else {
codeLine = error.stack;
}
showMessage_(error.message + ' Code line: ' + codeLine, 30);
throw error;
}
/**
* Shows a message in a pop-up.
*
* @param {String} message The message to show.
* @param {Number} timeoutSeconds Optional. The number of seconds before the message goes away. Defaults to 5.
*/
function showMessage_(message, timeoutSeconds) {
// version 1.0, written by --Hyde, 16 April 2020
SpreadsheetApp.getActive().toast(message, 'Custom script', timeoutSeconds || 5);
}function myFunction() {
Logger.log('1')
}
function createTimeDrivenTriggers() {
ScriptApp.newTrigger('myFunction')
.timeBased()
.everyMinutes(1)
.create();
}
function COUNTUNIQWORDS(cell) {
var result = []
var words = String(cell).split(" ");
var wordsCount = words.reduce(function (acc, w) {
if(acc[w]) {
acc[w] += 1
} else {
acc[w] = 1;
}
return acc;
}, {});
for (var w in wordsCount) result.push('Cлово "'+ w +'" встречается '+ wordsCount[w] + ' раз');
return result
}
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var counter = 6;
var logics = {
"F10": "=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;3;FALSE);0)",
"G10": "=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;4;FALSE);0)",
"H10": "=IF(C10>=10;F10-70;(F10+G10)-70)+J10",
"I10": "=H10+J10"
}
for (var key in logics) {
formulaPaste (list, counter, key, logics[key]);
counter++;
}
function formulaPaste (list, i, cell, formula){
list.getRange(cell).setFormula(formula);
var row = list.getLastRow();
var range = list.getRange(10, i, row-9);
list.getRange(cell).copyTo(range);
}function doGet(e){
var list = e.parameter.list;
function copyFormulas () {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
sheet.getRange("B2:B"+sheet.getLastRow()).setFormulaR1C1(sheet.getRange("B1").getFormulaR1C1());
}