How to automatically update the cell address in the google sheet script-CodePudding

I have built the script and it's working to convert cell "E6" on all sheets (except Test and Test2) to "value" on my file. Can someone help further improving the script for better level of automation (e.g auto change of cell address with auto scheduling of execution on 6th of Jan, Apr, Jun, Oct. If this can be done, full automation will be achieved).

To help understanding with following example:

Say on 6th Jan, I convert E6 to value Then on 6th Apr, I convert E12 to value Further then on 6th Jun, I convert E18 to value (with constant 6 rows a time, all on E Column)


// The script below is tested and is working,but need to change the cell address everytime

    function copyFormulasToValues() {
   var allSheetTabs,i,L,thisSheet,thisSheetName,sheetsToExclude,value;

  sheetsToExclude = ['Test','Test2'];

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  allSheetTabs = ss.getSheets();

  L = allSheetTabs.length;

  for (i=0;i<L;i  ) {
    thisSheet = allSheetTabs[i];
    thisSheetName = thisSheet.getName();

    //continue to loop if this sheet is one to exclude
    if (sheetsToExclude.indexOf(thisSheetName) !== -1) {continue;}


  var range = thisSheet.getRange("E6:E6");
  range.copyTo(range, {contentsOnly: true});
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  range.copyTo(range, {contentsOnly: true});
}

CodePudding user response：

I'm confused by what you're asking, but one thing I'm certain of is that this loop:

  for (i=0;i<L;i  ) {
    thisSheet = allSheetTabs[i];
    thisSheetName = thisSheet.getName();

    //continue to loop if this sheet is one to exclude
    if (sheetsToExclude.indexOf(thisSheetName) !== -1) {continue;}

Should look something like this instead:

  for (let sheet of ss.getSheets()) {
    if (sheet.getName() in sheetsToExclude) {
       continue;
    };
  };

CodePudding user response：

Save values on 6 Jan,Apr,Jun

function copyFormulasToValues() {
  const m = {0:"E6",3:"E12",5:"E18"};//month to ranges
  const exc = ['Test','Test2'];//exclude sheets
  const ss = SpreadsheetApp.getActive();
  const shts = ss.getSheets().filter(sh => !exc.includes(sh.getName()));
  const rg = m[new Date().getMonth()];//month to rg
  if(rg && rg.length > 0 && new Date().getDate() == 6) {
    shts.forEach(sh => {
      sh.getRange(rg).setValue(sh.getRange(rg).getDisplayValue())
    });
  }
}