Is there a simpler way to do this?-CodePudding

Is there a way I can loop through the cell letters instead of copy pasting and changing the cells?

Perhaps with an array?

I work in education, and these google sheets keep needing to be altered at my work and the following is how I have been doing it.

  var spreadsheet = SpreadsheetApp.getActive();
  
  spreadsheet.getSheets()[0].getRange("B21").setValue('3');
  spreadsheet.getSheets()[0].getRange('B22').setValue('MT1');
  spreadsheet.getSheets()[0].getRange('C22').setFormula('=C33'); 
  spreadsheet.getSheets()[0].getRange('C21').setValue(' ');
  spreadsheet.getSheets()[0].getRange("E21").setValue('3');
  spreadsheet.getSheets()[0].getRange('E22').setValue('MT1');
  spreadsheet.getSheets()[0].getRange('F22').setFormula('=f33'); 
  spreadsheet.getSheets()[0].getRange('F21').setValue(' ');
  spreadsheet.getSheets()[0].getRange("H21").setValue('3');
  spreadsheet.getSheets()[0].getRange('H22').setValue('MT1');
  spreadsheet.getSheets()[0].getRange('I22').setFormula('=I33'); 
  spreadsheet.getSheets()[0].getRange('I21').setValue(' ');
  spreadsheet.getSheets()[0].getRange("K21").setValue('3');
  spreadsheet.getSheets()[0].getRange('K22').setValue('MT1');
  spreadsheet.getSheets()[0].getRange('L22').setFormula('=L33'); 
  spreadsheet.getSheets()[0].getRange('L21').setValue(' ');

 }

CodePudding user response：

Here's a script I wrote that should work for you:

function setValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet =  spreadsheet.getSheets()[0];
  var range = sheet.getRange('B21:L22');
  var rangeArray = range.getValues();

  let ignoreCol = ['D','G','J']; //Ignore columns D, G, J
  
  for(var i in rangeArray) {
    //loop starts by going through rows, focusing on one row at a time.

    for(var j in rangeArray[i]) {
      //within each row, it calls each column.
      var val;

      let ix = Number(i)   Number(range.getRowIndex());
      let jx = Number(j)   Number(range.getColumn());
      let a1Range = sheet.getRange(ix,jx).getA1Notation();
      let colCheck = a1Range.slice(0,1); //Gets the column letter

      if(i == 0 && (j % 3 == 0 || j == 0)) val = '3';
      else if(i == 0) val = ' ';
      if(i == 1 && (j % 3 == 0 || j == 0)) val = `MT1`;
      else if(i == 1) val = `=${colCheck}33`;

      if(!ignoreCol.includes(colCheck)) { //Only moves on if the column is not ignored.
        sheet.getRange(ix,jx).setValue(val);
      }
    }
  }
}

I ran it a few times and it seems to be working properly.

Please let me know if you have any issues with this, or would like something explained.

CodePudding user response：

try, for the first 4 cells

  var sh = spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  sh.getRange("B21:C22").setValues([['3','MT1'],[' ','=C33']])