Update Destination Spreadsheet if the reference cell is blank-CodePudding

I have a code which updates the destination spreadsheet based on column A and column header

function updateShadowSKU() {
  var source      = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var destRange   = SpreadsheetApp.openById('104t29KUBGvglxmU-ILN0ntiNnZDkAkA4Lkm5jRoLJxk').getSheetByName('Sheet1').getDataRange();
  var destValues  = destRange.getValues();
  var destHeaders = destValues[0];
  var destIds     = destValues.map(e=>e[0]);
  var values      = source.getDataRange().getValues().map(e=>e.filter((f,i)=>!i||i>8));
  var colMap      = values[0].map(e=>destHeaders.indexOf(e));
  Logger.log(colMap);
  values = values.map((e,i,arr)=>e.map((f,j)=>[e[0],colMap[j],f,i,j])).flat().filter(e=>e[0]&&e[1]&&e[2]&&e[3]&&e[4]);
  Logger.log(values);
  if(!values.length){Logger.log('No Changes to make');return}
  values = values.map(e=>[destIds.indexOf(e[0]),e[1],e[2]]);
  
  Logger.log(values.length  ' changes to make');
  Logger.log(values);

  values.forEach(function (e){try{destValues[e[0]][e[1]]=e[2]}catch(err){}});
  destRange.setValues(destValues);
}

This script updates everything from the source sheet regardless if the data has been updated already or not and this causes slowness on the spreadsheet (updating about 50k cells)

my data structure is something like this, the red box (columns B to G) comes from the database, it tells if the item has already update or not

now, what I really want to happen is to push the data from columns I to N to destination spreadsheet IF the corresponding columns (B to G) is empty

example:

Cell I2 will be excluded since there is data in cell B2.

Cell J2 will be included since there is no data in cell C2

sample spreadsheet:

source: https://docs.google.com/spreadsheets/d/1O_M2g-dh5zG71OYNb_QOX3dzvhzYJxQgsH0EEzh8njQ/edit#gid=0

destination: https://docs.google.com/spreadsheets/d/104t29KUBGvglxmU-ILN0ntiNnZDkAkA4Lkm5jRoLJxk/edit#gid=0

CodePudding user response：

in destination file you can use query function as the following:

=QUERY(IMPORTRANGE("ur spreed sheet id","Sheet1!A1:G"),"select * 
where Col1 is not null and Col2 is not null and Col3 is not null 
and Col4 is not null and Col5 is not null and Col6 is not null and 
Col7 is not null",1)

if you want a specific column even if it is blank you can remove it from (where) statement. Note that this query returns an array, so make sure there is no override.

CodePudding user response：

Finally found an answer.

added this code block to check the destination cell if it has data on it or not, and if it has data, it will just skip that cell

values.forEach(function (e){
   try{
    if(destValues[e[0]][e[1]]==""){
       destValues[e[0]][e[1]]=e[2];
    }

the final code will be something like this one:

function updateShadowSKU() {
  var source      = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var destRange   = SpreadsheetApp.openById('104t29KUBGvglxmU-ILN0ntiNnZDkAkA4Lkm5jRoLJxk').getSheetByName('Sheet1').getDataRange();
  var destValues  = destRange.getValues();
  var destHeaders = destValues[0];
  var destIds     = destValues.map(e=>e[0]);
  var values      = source.getDataRange().getValues().map(e=>e.filter((f,i)=>!i||i>8));
  var colMap      = values[0].map(e=>destHeaders.indexOf(e));
  Logger.log(colMap);
  values = values.map((e,i,arr)=>e.map((f,j)=>[e[0],colMap[j],f,i,j])).flat().filter(e=>e[0]&&e[1]&&e[2]&&e[3]&&e[4]);
  Logger.log(values);
  if(!values.length){Logger.log('No Changes to make');return}
  values = values.map(e=>[destIds.indexOf(e[0]),e[1],e[2]]);
  
  Logger.log(values.length  ' changes to make');
  Logger.log(values);

  values.forEach(function (e){
   try{
    if(destValues[e[0]][e[1]]==""){
       destValues[e[0]][e[1]]=e[2];
    }catch(err){}});
  destRange.setValues(destValues);
}