Xtykutl

I'm trying to write a script in google sheets so that if I change a cell in column F, it will copy and paste values on the same row in columns b:e. I'd appreciate any help.

I have a google sheet where operators log events. They type in a reference number in column a and it looks up corresponding data (from another tab) and displays it in columns b-e. Then they add data like their name, the current time, etc in columns F-M. I'm trying to write a script so that when I change column F it copies the results from the formulas in columns b-e in the current row and pastes them back in place as values.

The reason is two-fold, one removing the formulas and pasting values improves performance and two if someone changed the lookup data the row becomes corrupted.

I found the following script that inserts a timestamp - seems like minor modifications would work but I haven't been able to figure it out. (thank you to the author of this)

//CORE VARIABLES

// The column you want to check if something is entered.

var COLUMNTOCHECK = 1;

// Where you want the date time stamp offset from the input location. [row, column]

var DATETIMELOCATION = [0,11];

// Sheet you are working on

var SHEETNAME = 'ReceivingLog'



function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  //checks that we're on the correct sheet.

  if( sheet.getSheetName() == SHEETNAME ) { 

    var selectedCell = ss.getActiveCell();

    //checks the column to ensure it is on the one we want to cause the date to appear.

    if( selectedCell.getColumn() == COLUMNTOCHECK) { 

      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);

      dateTimeCell.setValue(new Date());

      }

  }

}

Partially solved: The second function (below) works as a stand-alone to copy paste values, but it doesn't like the two functions strung together like this. Looking for help on how to have two scripts function together.

//CORE VARIABLES

// The column you want to check if something is entered.

var COLUMNTOCHECK = 1;

// Where you want the date time stamp offset from the input location. [row, column]

var DATETIMELOCATION = [0,11];

// Sheet you are working on

var SHEETNAME = 'ReceivingLog'



function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  //checks that we're on the correct sheet.

  if( sheet.getSheetName() == SHEETNAME ) { 

    var selectedCell = ss.getActiveCell();

    //checks the column to ensure it is on the one we want to cause the date to appear.

    if( selectedCell.getColumn() == COLUMNTOCHECK) { 

      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);

      dateTimeCell.setValue(new Date());

      }

  }

}



//CORE VARIABLES

// The column you want to check if something is entered.

var COLUMNTOCHECK2 = 2;

// Where you want the date time stamp offset from the input location. [row, column]

var DATETIMELOCATION2 = [0,-1];



// Sheet you are working on

var SHEETNAME2 = 'PurchaseOrders'



function onEdit2(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getActiveSheet();

  //checks that we're on the correct sheet.

  if( sheet.getSheetName() == SHEETNAME2 ) { 

    var selectedCell = ss.getActiveCell();

    //checks the column to ensure it is on the one we want to cause the date to appear.

    if( selectedCell.getColumn() == COLUMNTOCHECK2) { 

      var dateTimeCell = selectedCell.offset(DATETIMELOCATION2[0],DATETIMELOCATION2[1]);

      var data = dateTimeCell.getValues()

      dateTimeCell.setValue(data) , {contentsOnly: true};

      }

  }

}