Update Google Sheets script to automatically pull reference from new daily tab?-CodePudding

I am currently working on a spreadsheet that automatically duplicates and renames tabs to match each new day. This end result are tabs in the format of "12/05 SF" or "12/05 NYC". One of the central daily tabs references cells in the other city-specific ones via index match. Because the dates are strings rather than text, I don't know how to update the 12/05 references to 12/06, and so on for each new day:

spreadsheet.getCurrentCell().setFormulaR1C1('=index(\'12/05 SF\'!C6:C6,match("3a",\'12/05 SF\'!C3:C3,0))');

I see myself as having two options:

Figure out how to write a script that allows dynamic changing of the formula above
Rename the daily sheets to just "SF" and "NYC" so I don't have to update the formula

I've unsuccessfully tried to update the macro formulas to use a dynamic date for each tab rather than the static 12/05 date.

CodePudding user response：

To get the current date formatted as MM/dd you might use Utilities.formatDate(new Date(), spreadsheet.getSpreadsheetTimeZone(), 'MM/dd').

Example:

const cell = spreadsheet.getCurrentCell();
const date = Utilities.formatDate(new Date(), spreadsheet.getSpreadsheetTimeZone(), 'MM/dd');
const formula = `=index('${date} SF'!C6:C6,match("3a",'${date} SF'!C3:C3,0))`;
cell.setFormulaR1C1(formula);

Note: the formula declaration use template literal.

CodePudding user response：

Based on your information, can you try this in your formula,

=IMPORTRANGE("https://docs.google.com/spreadsheets/xxxxx", CONCAT(TEXT(TODAY(), "MM/DD")," SF!d6"))

Some explanations,

CONCAT(string 1, string 2)

joins two strings, e.g. CONCAT("12/08", " SF!d6") = "12/08 SF!d6".

TEXT(TODAY(), "MM/DD")

gets today's date in the format of month/day, e.g. 12/08.

Hope this works for you.