SearchForInvoice

search.gs

Array.prototype.findByPropName = function(name){
  for(var i = 0; i < this.length; i++){
      if(this[i].key == name) return this[i]
  }
  return {value: undefined};
}

function onOpen() {
  addMenu();
}

function addMenu() {
  SpreadsheetApp.getUi() 
      .createMenu('Оплаты')
      .addItem('Проверить', 'checkOrders')
      .addToUi();
}

function checkOrders() {
  var sh=SpreadsheetApp.getActive().getActiveSheet();
  var numRows=sh.getDataRange().getNumRows();
  for (var index=2; index<=numRows; index++) {
    var orderNum=sh.getRange(index, 4).getValue();
    var orderDate=new Date(sh.getRange(index, 5).getValue()); 
    var query="properties has {key='orderNum' and value='"+orderNum+"' and visibility='PUBLIC'}";
    var files=Drive.Files.list({q: query});
    for (var i=0; i<files.items.length; i++) {
      var currfile=files.items[i];
      var fileUrl=currfile.alternateLink;
      var savedDate=new Date(currfile.properties.findByPropName('orderDate').value);
      if ((orderDate.getDate()==savedDate.getDate())&&(orderDate.getMonth()==savedDate.getMonth())&&(orderDate.getFullYear()==savedDate.getFullYear()))
        sh.getRange(index, 9).setValue(fileUrl);      
    }
  }
}