Google-sheets – Check if value exists before copy-paste

google sheetsgoogle-apps-script

Can someone help please as I am new to Google Scripts.

I have this simple code that copies values from one sheet to another

function copyrevision() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var destSheet = ss.getSheetByName("Fallas Tecnicas");

  //DATE
  var source = ss.getRange ("D4");
  var destRange = destSheet.getRange(destSheet.getLastRow()+1,1);
  source.copyTo (destRange, {contentsOnly: true});

  //REPORT NUMBER
  var source2 = ss.getRange ("C4");
  var destRange = destSheet.getRange(destSheet.getLastRow(),2);
  source2.copyTo (destRange, {contentsOnly: true});

  //OBSERVACION
  var source6 = ss.getRange ("E49");
  var destRange = destSheet.getRange(destSheet.getLastRow(),6);
  source6.copyTo (destRange, {contentsOnly: true});
}

I need this operation to be done only if the report number value does not exist in the column 2 of destination sheet.

How can it be done?

Best Answer

First your script should read the values of Column 2

Let say that you use destSheet.getRange(2,2,sheet.getLastRow(),1).getValues() then you could use a JavaScript loop to iterate over all the values or use a Array method like Array.some(callback) that returns true if any of the values meet the callback test.

As getValues() return a 2D array, the statement could look as following

var valueToLookForFound = values.some(function(row){
  return row[0] === valueToLookfor;
}

valueToLookForFound will be true if the valueToLookFor was found.

References

Array.prototype.some()

Getting and setting datetime values

Whenever your script calls .getValue() on a cell formatted as a date, date-time, or duration, it gets a JavaScript Date object. You can then apply various methods listed on that MDN page to this object, including getMonth() for the month. Example:

  var sheet = SpreadsheetApp.getActiveSheet();
  var value = sheet.getRange('A1').getValue();  // is a Date object
  Logger.log([value.getMonth(), value.getHours(), value.getUTCHours()]); 
  // examples of available methods

Conversely, you can assign a Date object to a cell using setValue, and it will be automatically formatted as such.

  sheet.getRange('B2').setValue(new Date());  // current timestamp

Timezones

The timestamp in Google Sheets is in local timezone, which is set via File > Spreadsheet settings. The script also operates within a timezone, which may be different and is found under File > Project properties in the script editor. If these timezones don't agree, you'll have a problem with timestamps.

To check whether the timezones agree, you can either compare both manually, or do something like this:

var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1').setFormula('=date(2015,1,1)');
Logger.log(sheet.getRange('A1').getValue().getHours());

This inputs the formula =date(2015,1,1) in the cell A1. The result will be 2015-01-01 00:00:00 in the spreadsheet's time zone. The script then gets the date and extracts hours in the local time of the script. If the timezones agree, you should see 0.0 in the log.

Best Answer

Related Solutions

Google-sheets – Preventing paste from overwriting cell formatting in Google Sheets

Google Apps Script – Basic Date Manipulation in Google Script

Getting and setting datetime values

Timezones

Related Topic