How to use sheet name or date in cell to manipulate setFormula

user_11487107 0
Viewed 63

I see lots of for (var i + code written but I have never been able to understand it. I can set a formula but I dont know how to manipulate it.

My current struggle is that I want my script setFormula(=A1+B2+X) to work by grabbing a value and minusing 1 from it. As I understand it would probably end up as setFormula(=A1+B2+" + i + ")"

In my specific case, X can either be 1 of the 2 following (ideally the 2nd one) to be 24 (by getting the value of 25 and then minusing 1 from it). The date on the sheet is the 25th of the month is in cell A1, and the tab (sheet) name is xxx25. I need to be able to have X as 25-1: we want to end up with it printing =A1+B2+24 into the cell.

1: Day(A1)-1 (if the day in A1 was 25th of a month, this would give us the result of 24)

So in cell A1 on the current sheet it has DD MMMM YYYY so lets say if its the 25th of a month and year, it will set the formula in the sheet as =A1+B2+24

2: The tab (sheet) name is xxx25. This tab will always have 3 letters followed by the day of the month.

(In my case I have 1 for each day of the month, such as xxx1 xxx2 .. xxx9 xxx10. If this needs to be changed on my side to xxx01 xxx02 .. etc I can do that if required.)

So I guess this method would grab the last 2 values of the sheetname (the date, which in the example is 25th) so it extracts(?) it from xxx25 and now remembers 25, minuses 1 from it, and places it into the formula to become =A1+B2+24

Thanks, please let me know if any explanation more is needed.

google-apps-script google-sheets
2 Answers

Will this be a user-executed function that runs in the currently open sheet? If so, you could grab the active sheet's name and extract all but the first 3 characters from it:

function myFunc() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let cell = sheet.getRange('C1'); // Replace with the desired cell
  let sheetName = sheet.getName();
  let onlyDate = sheetName.substring(3);

  cell.setFormula(`=A1+B2+${onlyDate-1}`);
}
user_143219220

Using the value of A1:

Here I created a script that will create a custom menu. Whenever the menu is clicked, it will get the active range, use it to get the current sheet, get the value of A1, parse it and use its value in setFormula.

Code:

function onOpen() {
  /*
  This function will add a custom menu in Google Sheets
  named Set Formula and will execute setFormula function
  */
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Set Formula', 'setFormula')
      .addToUi();
}

function setFormula() {
  var range = SpreadsheetApp.getActiveRange(); //get the active range
  var sheet = range.getSheet(); //get the sheet of active range
  var date = sheet.getRange('A1').getDisplayValue(); //get the value of A1
  var day = date.split(" ")[0] //split using space and get the first element of array
  range.setFormula(`=A1+B2+${day-1}`);  //set formula to the active range
}

Demo:

enter image description here

References:

user_146060520
Related