I have a background in data analytics and have done a similar workflow in SQL but am brand new to Apps Script. I am a bit at a loss on where to even start in Apps Script. Any advice or pointing me in the direction of useful examples would be truly appreciated!

Currently, I have a reference table on one sheet with categories and values and the start and end date that value applies to. Then I have a data table on another sheet where I add an entry date and a category and I would like to have Apps Script write the corresponding value for that category on the date.

Reference table data (a blank end date means that is the current rate):

The data table where the entry date and the category are added manually over time. I want to use the reference table to write the value for that category for that entry date.

How do you get the query the reference data for that entry date and category to find the row with the corresponding value?

CodePudding user response：

Description

As I said I'm not good at QUERY but I finally got something to work. I'm sure other can improve on it.

First I created a named range TestQuery for the table of data. I could have just as easily used range "A1:D6"

Next I fill in the End Date with =TODAY() so it has a date value. Then I build my query.

=QUERY(TestQuery,"select B where ( ( A = '"&B11&"' ) and ( date '"&TEXT(A11,"yyyy-mm-dd")&"' > C ) and ( date '"&TEXT(A11,"yyyy-mm-dd")&"' < D ) )")

Reference

• Query Language
• Compare Dates in Query

CodePudding user response：

Getting data from a table on a sheet

function getData() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const values = sh.getRange("A2:D"   sh.getLastRow()).getValues();
  Logger.log(JSON.stringify(values));//2d array
}

A2 is assumed to be the upper left corner of the data