Alexander Ivanov

/**
 *  @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 '-';
}

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);
  }
}

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);
}

=ARRAYFORMULA(IFERROR(
  VLOOKUP(A1:A17;Shifts!A:H;{2\3\4\5\6};0);
  VLOOKUP(A1:A17;KPI!A:L;{4\5\2\12\3};0)
))

=ARRAYFORMULA(QUERY(
  SPLIT(FLATTEN(A1:A5& "|" & B1:B5 & "|" & C1:F5);"|");
  "where Col3<>''"
))

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")');

• Как ежедневно автоматически сохранять значение яче...
  
• Как автосохранять с нескольких листов на один в Go...
  
• Как ежедневно автоматически сохранять значение сто...
  

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);
}

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var csvMenuEntries = [{ name: 'export as csv files', functionName: 'userActionSaveAllSheetsAsCSV' }];
  ss.addMenu('csv', csvMenuEntries);
}

function userActionSaveAllSheetsAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var ssid = ss.getId();

  var fileInDrive = DriveApp.getFileById(ssid);
  var folderInDrive = fileInDrive.getParents().next().getId();

  const urls = sheets.map((sheet) => {
    const filename = `${sheet.getParent().getName()} ${sheet.getName()}.csv`;
    const array = sheet.getDataRange().getValues();
    const url = saveArrayToCSV_(array, filename, folderInDrive);
    return { url, filename, folderInDrive };
  });

  console.log(JSON.stringify(urls, null, '  '));
}

/**
 * @param {string[][]} array
 * @param {string} filename
 * @param {string} folderInDrive
 * @returns {string} The file url
 */
function saveArrayToCSV_(array, filename, folderInDrive) {
  const data = array.map((row) => row.join(';')).join('\n');
  const url = DriveApp.getFolderById(folderInDrive)
    .createFile(filename, data, 'text/csv')
    .getDownloadUrl()
    .replace('?e=download&gd=true', '');
  return url;
}