Google App Script - how to Copy Paste values ( only non empty cells) of a data range with , from a Spreadsheet to another Spreadsheet

R Upadhyay 0
Viewed 28

Objective - To copy all non-empty cells from a Spreadsheet-1 to Spreadsheet-2.

I saw some codes online and modified for my use, sharing below. I need help in getting the script right so that a variable range(i.e. to selects non-empty up to last rows and last columns) can be selected and data copied to Spreadsheet-2. I am trying to make it workable for 'A1:GZ1000' range. If numbers of rows / cols with non-zero (non-empty) increases, script needs to update range for passing values to Spreadsheet-2.

''''

   function dataImport(ssaID, ssbID) {
  // source doc
  var ssaID         = '1DZX1Jb4qwCHETtAENIsRioJvl4TC0hUWZDn_k1q3oes' ;
  var sheet = SpreadsheetApp.openById(ssaID);



  var ssbID         = '1HptKwYzdWCsz9oey9hOouhWNTQ3GAyu9oswZ1xgP3D0' ;
  
  // source sheet
  var ss = sheet.getSheetByName('Data - Price');

  // Get full range of data
  var SRange = ss.getDataRange();

  // get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  // get the data values in range
  var SData = SRange.getValues();
  // Logger.log(SData) 

  // target spreadsheet
  var tss = SpreadsheetApp.openById(ssbID);
  
  // target sheet
  var ts = tss.getSheetByName('Data - Price');

  // Clear the Google Sheet before copy
  ts.clear({ contentsOnly: true });
  Logger.log(SRange.length);
  // set the target range to the values of the source data
  var tsRange = ts.getRange(1,1,SRange.length,SRange[0].length);
  tsRange.setValues(Sdata);

}

'''' Pl. do ask for details if you require.

R U

google-apps-script
1 Answers

Try it this way:

function dataImport(ssaID, ssbID) {
  var ssaID = '1DZX1Jb4qwCHETtAENIsRioJvl4TC0hUWZDn_k1q3oes';
  var ss = SpreadsheetApp.openById(ssaID);
  var ssbID = '1HptKwYzdWCsz9oey9hOouhWNTQ3GAyu9oswZ1xgP3D0';
  var sh = ss.getSheetByName('Data - Price');
  var rg = sh.getDataRange();
  var vs = rg.getValues();
  var tss = SpreadsheetApp.openById(ssbID);
  var tsh = tss.getSheetByName('Data - Price');
  tsh.clearContents();
  tsh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}
user30hu0
Related