S
S
SAV21rus2022-01-21 17:46:04
Google Apps Script
SAV21rus, 2022-01-21 17:46:04

How to automatically save the value of columns in Google Sheets daily?

Having studied the topic https://qna.habr.com/answer?answer_id=1834719#answ... and https://qna.habr.com/answer?answer_id=2089582#answ... I tried to do the same only in relation to columns and displaying values ​​by date and by columns did not work.
The essence of a question is in tables List1 range A2:A25 the name of the data is written there, there is a range B2:B25 there the data. There is a Sheet2 range A2:A25 (does not match with Sheet1) the name of the data is written there, there is a range B2:B25 where the data itself is. I would like to transfer to Sheet3 a text value from Sheet1 A2:A25 and Sheet2 A2:A25 format data B1 - the date of data transfer and in cells B2:B25 the data itself.

function createTrigger() {
ScriptApp.getProjectTriggers().forEach(
(trigger) =>
trigger.getHandlerFunction() === 'saveData' &&
trigger.getEventType() === ScriptApp.EventType.CLOCK &&
(ScriptApp.deleteTrigger(trigger) ||
console.info(`Tirgger ${trigger.getUniqueId()} was deleted`))
);
// every minutes for testing
//ScriptApp.newTrigger('saveData').timeBased().everyMinutes(1).create();
// at 9 o'clock every days
ScriptApp.newTrigger('saveData').timeBased().atHour(17).everyDays(1).create();
//}
//function saveData() {
// const book = SpreadsheetApp.openById(
// 'IDтаблицы'
// );
// const sheet = book.getSheetByName('Лист1');
// const values = sheet.getRange('C3:AA3').getValues();
// book.getSheetByName('Лист2').appendRow([new Date(), values[0][0], values[0][1], values[0][2], values[0][3], values[0][4], values[0][5], values[0][6], values[0][7]]);
// console.info(`saveData was called successful`);
}
function saveData() {
const book = SpreadsheetApp.openById(
'IDтаблицы'
);
const date = new Date();
const sheetTotal = book.getSheetByName('Лист4');
const sheetGOOG = book.getSheetByName('Лист3');
const valuesGOOG = sheetGOOG.getRange('B2:B25').getValues().map(column => [date, 'Лист1', ...column]);
appendData_(sheetTotal, valuesGOOG);
console.info(`saveData was called successful`);
}

function appendData_(sheet, values) {
if (sheet.getLastColumn() === sheet.getMaxColumns())
sheet.appendRow([]);
sheet.getRange(sheet.getLastColumn() + 1, 1, values.length, values[0].length)
.setValues(values);
}


Here is what I managed to write so far, but it does not work correctly, I want to see the name in the range A2: A25 and in B1 the Date with the time of data transfer and in B2: B25 data
Help please, I just started to study Apps Script

Answer the question

In order to leave comments, you need to log in

1 answer(s)
A
Alexander Ivanov, 2022-02-17
@SAV21rus

As an option

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

Although it seems to me that I did not think through the little things, because The label for the field on each sheet must be unique. But this can be fixed by automatically adding the sheet name, e.g.
Example in Spreadsheet https://docs.google.com/spreadsheets/d/1la6H9omAvl...

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question