Google Sheets: Show values on a tab depending on date

Question

We have a restaurant and want to show the Menu on a screen with google sheets. I am using 2 tabs in Google sheets, The main (number 1) with the menu showing to the visitors, the second tab (number 2) has all the dates from today until coming 6 weeks like:

Column A	Column B	Column C	Column D
21 June	Appetizer this day	Main dish this day	Desert today
22 June	Appetizer this day	Main dish this day	Desert today
23 June	Appetizer this day	Main dish this day	Desert today

etc. etc.

Now what we want is that the 3 menu items are copied to the main tab on the day of today, so if it is 22 June, the corresponding appetizer, main dish and desert are copied to the main tab.

Is that possible?

Answer 1

This can be achieved with Google Apps Script. You can have a JavaScript code snippet that does this for you.

---

Open the required spreadsheet. Go to the Tools menu and click Script Editor. This will open up the script editor window with an empty function, code will have to be written inside this function, give it a name, I'm using updateFood():

function updateFood() {
      // Fetch all the sheets/tabs of the current spreadhsheet
      let sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

      // Select the second sheet/tab using [1], first tab will be [0]
      // Fetching the second sheet because that is where the data about the upcoming dates are present  
      let listSheet = sheets[1];

      // Get all the data in the sheet
      let data = listSheet.getDataRange().getValues();


     // Iterate over all data in the sheet
     for(i = 0; i < data.length; i++) {
        // Get the date of the current row and create a date object
        rowDate = new Date(data[i][0]);
       
        // Get today's date
        today = new Date();

        // Check if the row date is equal to today's date  
        if(rowDate.setHours(0,0,0,0) == today.setHours(0,0,0,0)) {
           
           // If the code reaches here, this row has the date same as today

           // Get the first tab/sheet and then I'm selecting the cells A1, B1 and C1
           // Modify the range as per which cells you want the dish names to be in
           let range = sheets[0].getRange("A1:C1");

           // Set the values of the selected three cells as the value of the second, third and fourth column in the current row of the 2nd tab which are Appetizer, Main dish and Dessert for that date respectively
           range.setValues([[data[i][1], data[i][2], data[i][3]]])
        }
     }
}

And that's all the code you need! Save it and then you can try clicking on run code and then checking the first tab to see the values updated. However, when you click run for the first time, Google will ask you to grant permissions to the script to access your sheet. Click Review Permissions and then click on your account. Then click Advanced and then click: .

But you will have to run this code every day. This can be achieved using a time-driven trigger.

In the same script editor window, look at the triggers button on the left:

Click on the + Add Trigger on the bottom right of the screen. And select the values as follows (change the time as required):

That's all you need. Make sure that in the second tab, create dates in the date column with the Excel DATE(YEAR, MONTH, DAY) function rather than manually typing in the dates.