function triggerSort(e) {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Sheet1');
sheet.sort(2, false).sort(1);
}
Диалоги пользовательского интерфейса не могут быть вызваны функциями, запускаемыми триггером, они должны запускаться действием пользователя, то есть щелчком по пункту меню или какой-либо кнопкой, которая вызывает функцию, отображающую пользовательский интерфейс.
/**
* Очищает B4, если L4 содержит значение "пусто".
* Работает на листе Sheet3
*/
function run() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Sheet3');
const valueL4 = sheet.getRange('L4').getValue();
if (valueL4 === 'пусто')
sheet.getRange('B4').clearContent();
}
function myFunction() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Лист');
const range = sheet.getRange('A1:L4');
const values = range.getValues();
range.setValues(calculate_(values));
}
/**
* @param {any[][]} values
* @returns {any[][]}
*/
function calculate_(values) {
return values.map((row, i) => {
const collection = row.slice(0, 3);
return row.map(((cell, j) => {
if (i < 1 || j < 3) return cell;
const header = values[0][j];
return collection.includes(header) ? 1 : '';
}));
});
}
function copyFiles(fromId, toId) {
var srcFldr = DriveApp.getFolderById(fromId);
var srcFiles = srcFldr.getFiles();
var desFldr = DriveApp.getFolderById(toId);
var desFiles = desFldr.getFiles();
var dfnA = [];
while (desFiles.hasNext()) {
var df = desFiles.next();
dfnA.push(df.getName());
}
while (srcFiles.hasNext()) {
var sf = srcFiles.next();
if (dfnA.indexOf(sf.getName()) == -1) {
sf.makeCopy(sf.getName(), desFldr);
}
}
}
function run(){
copyFiles('ASDFASDFSDF2345234ADSFASDF', 'ASDFSDFasf452345345SAFDF-sagasdfASDF');
}
Exception: You do not have permission to call SpreadsheetApp.openById. Required permissions: https://www.googleapis.com/auth/spreadsheets
/**
* @customfunction
*/
function COLORED(range) {
const book = SpreadsheetApp.getActiveSpreadsheet();
const formula = book.getActiveRange().getFormula();
const [skip, addr] = formula.match(/\((.*)\)/) || [undefined, undefined];
if(addr){
const range = (/!/.test(addr) ? book : book.getActiveSheet()).getRange(addr);
return range.getBackgrounds().map(row => row.map(cell => {
let res = ''
switch(cell){
case '#4285f4': res = 'Да';
break;
case '#ea4335': res = 'Нет';
break;
default:
break;
}
return res;
}));
}
return '-';
}
sheet
указывал на нужный лист, например, вместоconst sheet = SpreadsheetApp
.getActiveSpreadsheet()
.getSheetByName('Data')
const id = 'ABSDADFASDFSDFASDFASD123123';
const sheet = SpreadsheetApp
.getSpreadsheetById(id)
.getSheetByName('Data')
const id = 'ABSDADFASDFSDFASDFASD123123';
createNewGoogleDocs(id);
function triggerSction() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheetName =
Utilities.formatDate(new Date(), book.getSpreadsheetTimeZone(), 'dd.MM');
const sheet = book.getSheetByName(sheetName);
if (sheet) {
sheet.activate();
book.moveActiveSheet(1);
}
}
Range.isPartOfMerge()
function myFunction() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const range = book.getRange('C5');
const mergedRange = range.getMergedRanges()[0];
if (mergedRange) {
const countCells = mergedRange.getValues()[0].length * mergedRange.getValues().length;
console.log('Входит в объединенную ячейку?', range.isPartOfMerge());
console.log('Диапазон объединения:', mergedRange.getA1Notation());
console.log('Количество объединенных ячеек:', countCells);
console.log('Количество строк в объединенном диапазоне:', mergedRange.getValues().length);
} else {
console.info('Ячейка не объединена');
}
}
const sheetName = 'Sheet1';
const sheet = book.getSheetByName(sheetName);
if(sheet){
cnsole.log(`Лист с именем ${sheetName} есть`);
}
const sheetName = 'Sheet1';
const sheet = book.getSheetByName(sheetName) || book.insertSheet(sheetName);
function myFunction() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Sheet1');
const ranges = ['B1', 'B4', 'B6', 'B9'];
sheet.getRangeList(ranges).setBorder(null, null, true, null, null, null);
}
var n = 7;
var ThirtyDaysBeforeNow = new Date().getTime()-3600*1000*24*n;
var files = DriveApp.searchFiles(
'modifiedDate < "' + cutOffDateAsString +
'" and (mimeType = "application/zip" or mimeType = "image/jpg")');
image/jpg
и image/jpeg
, но не факт.Drive.Files.emptyTrash();
const SETTINGS = {
bookId: '1la6H9omAvlKuMkt-jwZGkp23K0wvV-9CXK9Kq1S0sTw',
dataSheetNames: ['Лист1', 'Лист2'],
archiveSheetName: ['Вывод данных'],
};
function createTrigger() {
ScriptApp.getProjectTriggers().forEach(
(trigger) =>
trigger.getHandlerFunction() === 'saveData' &&
trigger.getEventType() === ScriptApp.EventType.CLOCK &&
(ScriptApp.deleteTrigger(trigger) || console.info(`Tirgger ${trigger.getUniqueId()} was deleted`))
);
ScriptApp.newTrigger('saveData').timeBased().atHour(17).everyDays(1).create();
}
function saveData() {
const book = SpreadsheetApp.openById(SETTINGS.bookId);
const date = new Date();
const sheet = book.getSheetByName(SETTINGS.archiveSheetName);
// console.log(book);
SETTINGS.dataSheetNames.forEach((name) => {
const sheetD = book.getSheetByName(name);
const values = sheetD.getRange('A2:B').getValues();
saveData_(sheet, date, values);
});
console.info(`saveData was called successful`);
}
/**
* @param {globalThis.SpreadsheetApp.Sheet} sheet
* @param {Date} x
* @param {any[][]} values
*/
function saveData_(sheet, x, values) {
const tz = sheet.getParent().getSpreadsheetTimeZone();
const x_ = Utilities.formatDate(x, tz, 'dd.MM.yyyy');
const data = sheet.getDataRange().getValues();
const ys = data.map((row) => row[0]);
const xs = data[0].map((cell) => (cell && cell.getTime ? Utilities.formatDate(cell, tz, 'dd.MM.yyyy') : ''));
let indexX = xs.indexOf(x_);
if (indexX === -1) indexX = sheet.getLastColumn();
const preData = data.map((row) => [row[indexX] || '']);
preData[0][0] = preData[0][0] || x;
console.log(preData);
values.forEach((row) => {
if (row[0] !== '') {
const index = ys.indexOf(row[0]);
if (index !== -1) {
preData[index] = [preData[index][0] || row[1]];
}
}
});
console.log(JSON.stringify(preData));
sheet.getRange(1, indexX + 1, preData.length, 1).setValues(preData);
}