javascriptgoogle-sheetsgoogle-apps-script

Transfer data with GOOGLE SHEETS


This code can transfer data of last row from sheet1 to the last row of sheet2 increaseing rows in the Sheet2.

How I make for overwrite the data of sheet 2?

That is to say, do not increase rows in the sheet 2 with the transfers, keep 1 row in the Sheet2, deleting the previous data in each transfer.

Any Idea, i apreciate so much your indications.


function transferData() {
  var ssDb = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var ssAs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var lr = ssDb.getLastRow();
  var lc = ssDb.getLastColumn();
  var data = ssDb.getRange(3,1,lr-3,lc).getDisplayValues();
  var fData = data.map(data => {
    return [data[1], data[3], data[36], data[5], data[33], data[7], data[31], data[34], data[35], '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '','', '', '', '', '', '', '', '', '', '', '', ''];
  })
  ssAs.getRange(2,1,lr-3,37).setValues(fData);
  
}



Solution

  • Clear sheet before pasting new values

    To clear your Target sheet before pasting a new set of values you can just try this modification.

    function transferData() {
      var ssDb = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
      var ssAs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
      var lr = ssDb.getLastRow();
      var lc = ssDb.getLastColumn();
      var data = ssDb.getRange(3,1,lr-3,lc).getDisplayValues();
      var fData = data.map(data => {
        return [data[1], data[3], data[36], data[5], data[33], data[7], data[31], data[34], data[35], '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '','', '', '', '', '', '', '', '', '', '', '', ''];
      })
      ssAs.getRange(2, 1, ssAs.getMaxRows() - 1, ssAs.getMaxColumns()).clearContent();
      ssAs.getRange(2,1,lr-3,37).setValues(fData);
      
    }
    

    References: Apps Script clearContent