0

I would like to have it that when a row is edited that it only copies the name, phone and adds a timestamp of the edit on another tab in Google Sheets. Each row on Update sheet should be a new row from edit.

function onEdit(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Main');
  var date  = new Date();
  var rowIdx = sheet.getActiveRange().getRowIndex();
  var rowValues = sheet.getRange(rowIdx,1,1,sheet.getLastRow()).getValues();
  Logger.log(rowValues);
  var destValues = [];
  destValues.push(rowValues[0][0]);// copy data from col A to col A
  destValues.push(rowValues[0][2]);// copy data from col C to col B
  destValues.push(rowValues[0][1]);// copy data from col B to col C
  destValues.push(rowValues[0][3]);// copy data from col D to col D

  var dest = SpreadsheetApp.getActive().getSheetByName('Updates');
  dest.getRange(dest.getLastRow()+1,1,1,destValues.length).setValues([destValues]);



}

The above is working but i cannot figure out how to get the timestamp to show.

The outcome should display the roadname, phone and then C:C should have timestamp of change made.

I have updated the below sheet to show expected outcome

https://docs.google.com/spreadsheets/d/1V3Ne9L0kB97OkFKwlidx40yN__sx78aRik_Ujiej2Wc/edit#gid=1523701088

Expected outcome of 2nd Tab

Roadname Phone Timestamp
Edit 1 555-5555 2/16/2023 09:14:52
edit 2 555-5555 2/16/2023 09:14:52
Edit 3 555-5555 2/16/2023 09:14:52
Edit 4 555-5555 2/16/2023 09:14:52

Data Table:

Road Name Date Contacted Phone number Clan Name State Status Comment
Name2 2/1/23 5551212222 Some Comment 1
Name1 2/1/23 5551212222 Some comment 2
Name4 2/1/23 5551212222 Called him
2

1 Answer 1

0

Try something like this:

/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error('Please do not run the onEdit(e) function in the script editor window.');
  }
  let sheet;
  if (e.range.columnStart !== 7 || !(sheet = e.range.getSheet()).getName().match(/^(Main)$/i)) {
    return;
  }
  const rowValues = sheet.getRange(e.range.rowStart, 1, 1, sheet.getLastRow()).getValues().flat();
  const destValues = [0, 2].map(index => rowValues[index]).concat([new Date()]);
  e.source.getSheetByName('Updates').appendRow(destValues);
}

See Sheet.appendRow(), Apps Script at Stack Overflow and Clean Code JavaScript.

1
  • Thank you for your answer. Worked great. Feb 16 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.