CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Julien
Julien

Reputation: 81

Copy value to another sheet with a condition

I feel like today I'm not able to do anything with my script. I'm trying to do a simple test, if the date in cell1 is equal to the date in cell2 then I will copy and paste the data into another file, in a specific sheet. If cell1 is different than cell2, I will copy the data in the same other file but in a different sheet. I can't make it work. What am I doing wrong? thanks for the help, j.

function copytransactionTodispatchN() {
  var sss = SpreadsheetApp.getActiveSpreadsheet();; // sss = source spreadsheet
  var ss = sss.getSheetByName("Nouveau Client - Transaction du jour"); // ss = source sheet
  var ssn = sss.getSheetByName("Nouveau Client"); // ss = source sheet
  //Get full range of data
  var SRange = ss.getRange('A2:f2');
  var SData = SRange.getValues();
var dateachat = ssn.getRange('c20').getValues;
var datelivraison = ssn.getRange('e31').getValues;

 if(dateachat == datelivraison){ 
    var tss = SpreadsheetApp.openById("idoftheotherfile"); // tss = target spreadsheet
    var ts = tss.getSheetByName("sheet1"); // ts = target sheet
    ts.getRange("b"+(getLastDataRow(ts)+1)+":i"+(getLastDataRow(ts)+1)).setValues(SData); 
    } 
    else {var tss = SpreadsheetApp.openById("idoftheotherfile"); // tss = target spreadsheet
          var ts1 = tss.getSheetByName("Sheet2"); // ts = target sheet
        //set the target range to the values of the source data
        ts1.getRange("a"+(getLastDataRow(ts)+1)+":h"+(getLastDataRow(ts)+1)).setValues(SData);
        }
}

function getLastDataRowN(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange("b" + lastRow);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

Upvotes: 0

Views: 658

Answers (2)

Julien
Julien

Reputation: 81

Ok, the mistakes were :

  1. getlastrow function was not working for condition 2
  2. the test of the date was not woking, I had to replace if(dateachat == datelivraison.) by if(dateachat.valueOf() == datelivraison.valueOf())

And now it is all good. Thank you all!!

 var sss = SpreadsheetApp.getActiveSpreadsheet();; // sss = source spreadsheet
 var ss = sss.getSheetByName("Nouveau Client - Transaction du jour"); // ss = source sheet
 var ssn = sss.getSheetByName("Nouveau Client"); // ss = source sheet
 //Get full range of data
 var SRange = ss.getRange('A2:h2');
 var SData = SRange.getValues();
var dateachat = ssn.getRange('c20').getValues;
var datelivraison = ssn.getRange('e31').getValues;

if(dateachat.valueOf() == datelivraison.valueOf()){ 
   var tss = SpreadsheetApp.openById("IDtarget"); // tss = target spreadsheet
   var ts = tss.getSheetByName("Sheet1"); // ts = target sheet
   ts.getRange("b"+(getLastDataRowb(ts)+1)+":i"+(getLastDataRowb(ts)+1)).setValues(SData); 
   } 
   else {var tss = SpreadsheetApp.openById("IDtarget"); // tss = target spreadsheet
         var ts = tss.getSheetByName("Sheet 2"); // ts = target sheet
       //set the target range to the values of the source data
       ts.getRange("a"+(getLastDataRowa(ts)+1)+":h"+(getLastDataRowa(ts)+1)).setValues(SData);
       }
}

function getLastDataRowb(sheet) {
 var lastRow = sheet.getLastRow();
 var range = sheet.getRange("b" + lastRow);
 if (range.getValue() !== "") {
   return lastRow;
 } else {
   return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
 }              

} 

function getLastDataRowa(sheet) {
 var lastRow = sheet.getLastRow();
 var range = sheet.getRange("a" + lastRow);
 if (range.getValue() !== "") {
   return lastRow;
 } else {
   return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
 }              

}```

Upvotes: 1

Scott
Scott

Reputation: 15

var dateachat = ssn.getRange('c20').getValues;

Variable ssn isn't defined anywhere, which is likely causing dateachat to be undefined or null.

Upvotes: 0

Related Questions