copy paste range into next column and update

Question

column B gets data updated

column C onwards is used to store consolidated data

im trying to copy a range of data to right next column

and update column C with data from column B

function doUpBCL() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BLC');

  var data_new = ss.getRange('B1').getDisplayValue();  
  var data_old = ss.getRange('C1').getDisplayValue();  

  if( data_new !== data_old )  // check if data changed
  {

  var lr = ss.getLastRow();
  var lc = ss.getLastColumn();
    
  var data = ss.getRange(1,3,lr,lc - 3).getValues();
  ss.getRange(1,4,lr,lc - 4).setValues(data);

  var data_ = ss.getRange(lr,2,1,1).getValues();
  ss.getRange(lr,3,1,1).setValues(data_);
    
  }   

};

macro (created by Google Sheets) as EXAMPLE to ilustrate what i want is (i need help with above function, not this)

function upblc_macro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('BLC'), true);
  spreadsheet.getRange('D:D').activate();
  spreadsheet.getRange('C:Z').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('C:C').activate();
  spreadsheet.getRange('B:B').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

using last row cos im going to use similar funcions to other pages and im trying to funcion where i dont have to adjust everytime

simply add new column, move or append to last column wont work the way i need

help is much appreciated, thanks

This doesn't make sense: spreadsheet.getRange('D:D').activate(); spreadsheet.getRange('C:Z').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); D:D is one column C:Z is 24 columns — Cooper
that macro was created by google sheet. i selected the range i wanted to copy, then selected start destination, then paste, basicly coping everything 1 column to the right, trying to create a function script to do that, but its not working — Bruno Carvalho
function copyCol() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C:C').activate(); spreadsheet.getRange('D:D').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); }; — aNewb
forget about that macro, that was just to ilustrate my intention, the above function is what i actually need help — Bruno Carvalho

Bruno Carvalho Bruno Carvalho · Accepted Answer · 2021-01-08T01:22:10

with lots of tries and errors, with a few tweaks and minor changes, i managed to make it work as i wanted

function doUpBCL() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('BLC');
  
  const data_new = ss.getRange('B1').getDisplayValue();  
  const data_old = ss.getRange('C1').getDisplayValue();  

  if( data_new !== data_old )  // check if data changed
  {

  var lr = ss.getLastRow();
  var lc = ss.getLastColumn();
    
  const data = ss.getRange(1,3,lr,lc-2).getValues();
  ss.getRange(1,4,lr,lc-2).setValues(data);

  const data_ = ss.getRange(1,2,lr,1).getValues();
  ss.getRange(1,3,lr,1).setValues(data_);
    
  }  
  
  else
    {
    }

};

not sure why of some stuff i changed, some other stuff i learned, but still learning

copy paste range into next column and update

1 Answers