I wanted to create a google sheet with columns that look like this: Session 1 Date, Session 1 notes, Session 1 Status, Session 2 Date, Session 2 notes, Session 2 Status, Session 3 Date....and so on for 100 sessions. Where all dates columns have the date validation rule and status columns have the List of Items (Completed, No Show) validation rule.

Any idea how to make this sheet on the AppScript?

I have tried doing it manually but it simply isn't practical for 300 columns.

CodePudding user response：

You can do it for row#2 after manually defining the validation rule for B2 and D2

Please, make sure that you have enough columns for 300 sets of data

Then copy manually row#2 to others (only validation rules)

function setRules() { 
  var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var rangeNewDataValidation = sh.getRange('B2');
  var rule = rangeNewDataValidation.getDataValidations();
  var newRule = rule[0][0].copy();
  for( var j=5; j<=sh.getMaxColumns(); j =3){
    var range = sh.getRange(2,j);
    range.setDataValidations([[newRule.build()]]);
  }

  var rangeNewDataValidation = sh.getRange('D2');
  var rule = rangeNewDataValidation.getDataValidations();
  var newRule = rule[0][0].copy();
  for( var j=7; j<=sh.getMaxColumns(); j =3){
    var range = sh.getRange(2,j);
    range.setDataValidations([[newRule.build()]]);
  }

}

CodePudding user response：

Making Groups and Formatting them

Code:

function makinggroups() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Template");
  //GroupName   Names   GroupType   Validation  FormatType  Format  Rows    Groups  Sheets
  let col = {};
  let idx = {};
  const hA = sh.getRange(1, 1, 1, sh.getLastColumn()).getDisplayValues().flat().forEach((h, i) => { col[h] = i   1; idx[h] = i; });
  const shsr = 2;
  const rg = sh.getRange(shsr, 1, sh.getLastRow() - shsr   1, sh.getLastColumn())
  const vs = rg.getValues();
  const shts = vs[0][idx["Sheets"]].split(',').map(e => e.trim());
  const rows = vs[0][idx["Rows"]];
  const grps = vs[0][idx["Groups"]];
  const fmts = sh.getRange(shsr,col["Format"],sh.getLastRow() - shsr   1).getNumberFormats().flat();
  shts.forEach(name => {
    let sh = ss.getSheetByName(name);
    let hrow = [];
    [...Array.from(new Array(grps).keys(), x => x   1)].forEach(n => {
      [...Array.from(new Array(vs.length).keys())].forEach(gidx => {
        hrow.push(vs[gidx][idx["GroupName"]].toString().length ? `${vs[gidx][idx["GroupName"]]} ${n} ${vs[gidx][idx["Names"]]}`.trim() : `${vs[gidx][idx["Names"]]}`.trim());
        if (vs[gidx][idx["Validation"]].toString().length) {
          let r = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInList(vs[gidx][idx["Validation"]].toString().split(',').map(e => e.trim())).build();
          sh.getRange(shsr, n * (gidx   1), rows).setDataValidation(r);
        }
        sh.getRange(shsr, n * (gidx   1), rows).setNumberFormat(fmts[gidx]);
      });
    });
    sh.clear();
    sh.getRange(1, 1, 1, hrow.length).setValues([hrow]).setFontWeight("bold");
    SpreadsheetApp.flush();
  });
}

Template Sheet

Contains dataValidation and formatting information.

Template Sheet Image:

You will need the Template Sheet in order to run the code. You will want to change the number of groups to 100. And you may wish to change the number and names of Sheets. You will also have to insert the appropriate formats in the formats column since I can't transfer that easily.

Note: Do not change the header names as these are used inside of the code to determine column indexing.

Production Sheet: