Add Current Date into first column for each row on array based second column

Far 0
Viewed 33

Basically, this script copies the json data, if a value in a column exists, the value is replaced, but if not then the value is copied at the end of the row. it's just that I want to add the date in the first column, if there is, then the date will overwrite the previous date, if the data is not the same it will be copied in the next row, here's the script

function pasteDAta(dataReq) {
  const id = "1_27rjNQmlXrwVKpLWUbGrJYPJufGRa7Dk-XEKcNAHr0";
  var sheet = SpreadsheetApp.openById(id).getSheetByName("Sheet1");

  // get header and the rest data from the sheet
  var [ headings, ...data ] = sheet.getDataRange().getValues();

  var values = dataReq.map((a) => {
    let holder = [];
    for (x in headings) {
      let output = headings[x] in a ? a[headings[x]] : "";
      holder.push(output);
    }
    return holder;
  });
  var len = values.length;

  var new_values = []; // values to add at the bottom of the sheet
  var col_b = data.map(x => x[1]);  // get column B from the data

  values.forEach(row => {
    // find an index of the row with the same value in cell B
    var row_index = col_b.indexOf(row[1]);

    // if nothing was found add the row to the new values
    if (row_index == -1) new_values.push(row); 

    // else change the found row on the sheet
    else sheet.getRange(row_index + 2, 1, 1, row.length).setValues([row]); // '+2' due the header
  }) 

  // add the new values at the bottom of the sheet
  if (new_values.length > 0)
    sheet.getRange(sheet.getLastRow() + 1, 1, len, new_values[0].length).setValues(new_values);

  return "Numbers of sheets added: " + len;
}

How do I add the current date into column A based column B according to script conditions ;

date google-apps-script json arrays
1 Answers

If you want to add a date to column A whenever a row is updated, you just have to use the Date constructor to get current date and assign row[0] to it:

else {
  row[0] = new Date();
  sheet.getRange(row_index + 2, 1, 1, row.length).setValues([row]);
}

If you wanted to add the date to appended rows, you'd add row[0] = new Date(); before the corresponding if...else block.

Iamblichus0
Related