I'm looking to create templates using GAS -- all I need is to copy and paste variables into designated cells, push the data, then move to the next.

I had my script working great for a single template (the first set of variables). I tried to set up a loop, and now I'm hitting a "Range not found" error I'm going crazy trying to figure out.

I had it working copying and pasting the variables out and generating the template, but struggling to get it to move to the next cell in each column.

I made a range for each column where the script should loop through, meaning:

Row 2 Col A, Row 2 Col B, Row 2 Col C, Row 2 Col D each go through the loop (variables get pasted, sheet gets created, etc), then we move onto Row 3 Col A, Row 2 Col B, so on so forth.

function CreateTemplates() {
   var templatesNeeded = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test');            
   var rundownSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Exclusive Template Creation');
   var tabname = templatesNeeded.getRange('A2').getValue();
   var pasteTemplate = rundownSheet.getRange('e1:l150').getValues();

    const row = 2;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Test');
  const rows = sheet.getLastRow() - row   1;
  const range = sheet.getRange(row, 1, rows, 1);
  const values = range.getValues().flat();

 const dateRange = sheet.getRange(row, 2, rows, 1);
  const dateValues = dateRange.getValues().flat();


   const gameRange = sheet.getRange(row, 3, rows, 1);
  const gameValues = gameRange.getValues().flat();


   const typeRange = sheet.getRange(row, 4, rows, 1);
  const typeValues = typeRange.getValues().flat();

     const broadcastRange = sheet.getRange(row, 5, rows, 1);
  const broadcastValues = broadcastRange.getValues().flat();

  const matches = {};
  values.forEach((value, i) => {
    if (value !== '') {

  
 rundownSheet.getRange('E3').setValue(templatesNeeded.getRange(values).getValue());
   rundownSheet.getRange('B1').setValue(templatesNeeded.getRange(dateValues).getValue());
   rundownSheet.getRange('B2').setValue(templatesNeeded.getRange(gameValues).getValue());
   rundownSheet.getRange('B3').setValue(templatesNeeded.getRange(typeValues).getValue());
rundownSheet.getRange('H2').setValue(templatesNeeded.getRange(broadcastValues).getValue());

 
     var tss = SpreadsheetApp.openById('16nn9mfSOpVbPZBsB_nwhRNRv4nYQblrS1GzAD4jgmoQ');

 var sheet = tss.getSheetByName('Single Template');


 sheet.copyTo(tss).setName(tabname);


 
// Logger.log(value)

  sheet.getRange(1,1,pasteTemplate.length,pasteTemplate[0].length).setValues(pasteTemplate);



Utilities.sleep(1000);
    }
  });
}

I think it's clearly a problem with how I created my ranges (or something with the loop). Any thoughts are greatly, greatly appreciated.

Here's a test link where the code would be hosted:

https://docs.google.com/spreadsheets/d/1WeBgyi8JVfC02rCIB0T1eEbbmtpaXYF16qM7MLIzqPs/edit#gid=0

Here's a test link where the code will be pushed to (templates will be created here):

https://docs.google.com/spreadsheets/d/16nn9mfSOpVbPZBsB_nwhRNRv4nYQblrS1GzAD4jgmoQ/edit#gid=804269175

Large improvement, but still hitting some errors. Now, it's looping through and creating the tabs, but the setValues to actually move the data over doesn't seem to work. Just creating blank tabs, although on the first sheet I'm seeing the values being generated. I added in utilities.sleep but still playing with it:

function createNewTemplates() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Test');
  const sh2 = ss.getSheetByName('Exclusive Template Creation');
  const sh1A2 = sh1.getRange('A2').getValue();
  const vs2 = sh2.getRange('e1:l150').getValues();
  const sr = 2;
  // const sh1 = ss.getSheetByName('Test');
  const vs1 = sh1.getRange(sr, 1, sh1.getLastRow() - sr   1, 5).getValues();
  const tss = SpreadsheetApp.openById('16nn9mfSOpVbPZBsB_nwhRNRv4nYQblrS1GzAD4jgmoQ');
  vs1.forEach((r, i) => {
    if (r[0] !== '') {
      sh2.getRange('E3').setValue(r[0]);
      sh2.getRange('B1').setValue(r[1]);
      sh2.getRange('B2').setValue(r[2]);
      sh2.getRange('B3').setValue(r[3]);
      sh2.getRange('H2').setValue(r[4]);
      Utilities.sleep(2000);
      let sh = tss.getSheetByName('Single Template');
      sh.copyTo(tss).setName(r[0]);
      sh.getRange(1, 1, vs2.length, vs2[0].length).setValues(vs2);
    }
  });
}

CodePudding user response：

Try this:

function CreateTemplates() {
  const ss = SpreadsheetApp.getActive();
  const sh1 = ss.getSheetByName('Test');
  const sh2 = ss.getSheetByName('Exclusive Template Creation');
  const sh1A2 = sh1.getRange('A2').getValue();
  const vs2 = sh2.getRange('e1:l150').getValues();
  const sr = 2;
  const vs1 = sh1.getRange(sr, 1, sh1.getLastRow() - sr   1, 5).getValues();
  const tss = SpreadsheetApp.openById('linkID');
  const sh = tss.getSheetByName('Single Template');
  vs1.forEach((r, i) => {
    if (r[0] !== '') {
      sh2.getRange('E3').setValue(r[0]);
      sh2.getRange('B1').setValue(r[1]);
      sh2.getRange('B2').setValue(r[2]);
      sh2.getRange('B3').setValue(r[3]);
      sh2.getRange('H2').setValue(r[4]);
      sh.copyTo(tss).setName(sh1A2);//this does not make sense
      sh.getRange(1, 1, vs2.length, vs2[0].length).setValues(vs2);
    }
  });
}