CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jump_Ace
Jump_Ace

Reputation: 303

Insert Month In Plain Text

I have this sheet where Column B is a column for the month. I have a script (below) that is inserting the month from cell B1 ('=TODAY()'). The script is (mostly) working, however, it's updating the month as time progresses instead of staying with the original month when the script first ran.

Is there a way to modify my script so that it permanently inserts the month in plain text for when the script first ran? I'm not sure how to accomplish that without using a formula from B1. Thanks in advance!

function Month() {

  const TARGET_COLUMN = 2;

  // Get Source Spreadsheet
  var source = SpreadsheetApp.getActiveSpreadsheet();

  // Get Source Sheet from Spreadsheet
  var source_sheet = source.getActiveSheet();

  // Get Last Row
  var lastRow = source_sheet.getLastRow();

  var columnB = source_sheet.getRange(3,3,lastRow);
  var valuesB = columnB.getValues();

 valuesB.forEach((valueB,rowNumber) => {
    var rowNumbers = rowNumber+3;
    if (valueB != "") {
    source_sheet.getRange(rowNumbers,TARGET_COLUMN).setFormula('=IF(ISBLANK(C'+rowNumbers+'), "", DATE(YEAR($B$1), MONTH($B$1), DAY($B$1)))');
    }
    else {
      source_sheet.getRange(rowNumbers,TARGET_COLUMN).setFormula(null);
    } 
  })
}

Upvotes: 0

Views: 36

Answers (1)

Jump_Ace
Jump_Ace

Reputation: 303

With some Googling, I figured it out. It will insert Today's date in column B (via cell B1) and I just had Sheets format Column B to show just the month of today's date.

function Month() {

  const TARGET_COLUMN = 2;

  // Get Source Spreadsheet
  var source = SpreadsheetApp.getActiveSpreadsheet();

  // Get Source Sheet from Spreadsheet
  var source_sheet = source.getActiveSheet();

  // Get Last Row
  var lastRow = source_sheet.getLastRow();

  var columnB = source_sheet.getRange(3,3,lastRow);
  var valuesB = columnB.getValues();

  //Get Month
  var root = source_sheet.getRange("B1").getValue();
  var month = root.getMonth();
  root.setMonth((month) % 12);

 valuesB.forEach((valueB,rowNumber) => {
    var rowNumbers = rowNumber+3;
    if (valueB != "") {
    source_sheet.getRange(rowNumbers,TARGET_COLUMN).setValue(root);
    }
    else {
      source_sheet.getRange(rowNumbers,TARGET_COLUMN).setFormula(null);
    } 
  })
}

Upvotes: 1

Related Questions