Google Apps Script to replace formula with text in the current row

Question

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

Roger Roger · Accepted Answer · 2018-11-09T21:53:03

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

Google Apps Script to replace formula with text in the current row

1 Answers