Need help with code.

Hi all! Need help with code... There are goods/services. The data on them changes daily (in the example, the RANDBETWEEN function is used for this). On another sheet, this data is transferred to a line. It is necessary once a day (a trigger is configured for this) to transfer data from this line to the nearest empty line corresponding to the date. I wrote the code in Apps Script, but it transfers only one value to all products. What did I write wrong? Help, good people.

Link to file

function ProductHistory() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("История данных");
  var value = sheet.getRange('B1:L1').getValues();
  var range = sheet.getRange('B1:L').getValues();
  var lastRow = null;
  var i = 0;
  while(i < range.length) {
    if (range[i][0] !== "") {
      i  ;
    } else {
      lastRow = i  1;
      break;
    }
  }
  sheet.getRange(`B${lastRow}:L${lastRow}`).setValue(value)
}

CodePudding user response：

You should filter out blank values in the source data before writing to the history sheet. Once you have the data, use Sheet.appendRow(). To run the function script daily, create a time-driven trigger.

You may want to try the appendValuesToArchiveSheet script with these parameters:

// [START modifiable parameters]
var rangeToLog = 'Данные!B2:B';
var sheetToLogTo = 'История данных';
// [END modifiable parameters]

The script will append new rows at the bottom of existing data. Your column A contains =IF(A4<TODAY();A4 1;"") formulas that you will have to delete so that new data can be written immediately below existing data. The script will automatically insert timestamps on the rows it writes.