How to fill the cells that match a criteria with Apps Script?

Question

I am new in Apps Script, but I do love the possibilities it offers!

I have a question about how to fill the cells in a specific array with a value set in another sheet. I have two sheets: 1. Billing: lists the projects (column A) with the date on which the invoice is sent (column B) 2. Invoice: is the sheet that is sent as a pdf (I have the script for that). It only displays the list of the projects whose invoice has not yet been sent (basic FILTER function).

I want to set on the sheet "Billing", column B, the date that appears in the sheet "Invoice". My "problem" is that I don't want to fill the whole column, but only the cells that match this criteria: there is a project in column A, and there is no date in column B.

This is a sheet to understand what I'm looking for:

This is certainly easy to do, but since I'm starting with Apps Scripts, I don't know how to do it. Your help will be greatly appreciated.

So far, I've done this very little code to get the date:

function getDate() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var date = ss.getSheetByName('Invoice').getRange('D7').getValue();
}

Rafa Guillermo Rafa Guillermo · Accepted Answer · 2019-07-16T10:45:36

This function grabs the date from the cell D7 in your Invoice sheet, then iterates down column B in your Billing sheet and inputs the date in the cell if the cell in column A in the same row is empty:

function putDateInBilling() {

  var direction = SpreadsheetApp.Direction.DOWN  
  var invoiceDate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Invoice').getRange('D7').getValue();
  var billingData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Billing').getRange('A3:A').getNextDataCell(direction);

  for (var row = 3; row <= billingData.getRow(); row++){     
    if (SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Billing').getRange(row, 2, 1, 1).isBlank())      
      SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Billing').getRange(row, 2, 1, 1).setValue(invoiceDate).setNumberFormat("yyyy-MM-dd");
  }    
}

How to fill the cells that match a criteria with Apps Script?

1 Answers