CopyTo in Sheets Stops Working without Error

Question

Edit: Apologies for the confusion. This has been solved and is not related to the code below. There was a "clear bulksheet" function that deleted values up to 900 rows. Unfortunately, what happened was that the first script went beyond those 900 rows and so while it appeared everything was deleted it was not. So the function went down and started copy/pasting after all the data remaining past 900. I scrolled a few hundred rows but not all the way. Turns out it was working the whole time, I just couldn't see it :D

---

I have a Google Apps Script that loops through a column, sets a value to a template (in a separate sheet) and then copy/pastes that template to the first empty row in another sheet. All sheets are in the same workbook as separate tabs.

The function works fine the first time and is replicated identically across several different "Templates" where it still functions. I've duplicated the sheet (which duplicates the code) and it still doesn't work. The only work around I've found is to create an entirely new sheet and put the code into it.

The first function copies the template to the bulksheet.

The second function loops through a column with x rows of text. Grabs the value of text from the column and pastes to the template from the column and sets the value in the template. Then it calls the first function.

I've confirmed that the second function is looping through and setting the values in the template so the function appears to be running but the template never ends up being pasted.

This seems to happen randomly so I'm not sure if it's a bug or a caching issue. I successfully ran the script the first time to set/copy/paste over 100 times. Then it broke.

I've tried to flush but I'm not sure if I'm doing that correctly in terms of where to put it (I've tried a few spots). I've changed out var and let to see if that was an issue.

Again, no errors are thrown and the function runs to completion.

Appreciate any help!

function CopyTemplateToBulkSheet() {
  
  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let templateSheet = ss.getSheetByName('Template-make a copy');
  let copyRange = templateSheet.getRange('A2:U7')
  
  let BulkSheet = ss.getSheetByName('BulkSheet');
  let pasteRange = BulkSheet.getRange(BulkSheet.getLastRow()+1, 1);

  copyRange.copyTo(pasteRange, {contentsOnly:true});
}


function Loopthrough(){

  Logger.log('Beginning Campaign Creation')
  let ss = SpreadsheetApp.getActiveSpreadsheet();

  let templateSheet = ss.getSheetByName('Template-make a copy')

  let keywordrange = ss.getRange('H3:H').getValues();
  let targetkeywords = keywordrange.filter(String);

  targetkeywords.forEach(function(value){
    templateSheet.getRange('L5').setValue(value)
    CopyTemplateToBulkSheet();
})
}

Answer 1

I think one problem might be that you're not selecting a sheet when you try to get the data range for the keywords (getRange('H3:H')). You need to getSheetByName first, and assign that to a dataSheet variable before getting the range for the keywords.

Another problem might be that you're getting two separate instances of the Spreadsheet/individual sheets in each of the functions, so CopyTemplateToBulkSheet doesn't see the changes for each keyword, so it just works on the first one.

I rearranged the code into a single function, and this works:

// Add the script to the file menu
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Copy to Bulk Sheet', functionName: 'copyKeywordValuesToBulk'}
  ];
  spreadsheet.addMenu('Keyword Copy', menuItems);
}

function copyKeywordValuesToBulk(){
  Logger.log('Starting script');

  // Get the sheets
  var document = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = document.getSheetByName('Data');
  var templateSheet = document.getSheetByName('Template');
  var bulkSheet = document.getSheetByName('Bulk');

  // Get the keywords
  var targetKeywords = dataSheet.getRange('H3:H').getValues().filter(String);

  // Copy the data for each keyword
  Logger.log(`Copying ${targetKeywords.length} keywords`);
  targetKeywords.forEach(keyword => {
    // Set the current keyword value so the formulas create the values we want to copy
    templateSheet.getRange('L5').setValue(keyword);

    // Get the source and destination ranges
    var sourceRange = templateSheet.getRange('A2:U7');
    var destRange = bulkSheet.getRange(bulkSheet.getLastRow()+1, 1);

    // Add rows to make space in the sheet, so we don't run out of rows
    bulkSheet.insertRowsAfter(bulkSheet.getLastRow(), sourceRange.getNumRows());

    // Actually copy the data
    sourceRange.copyTo(destRange, {contentsOnly:true});
  })
}

This assumes that your sheet names are Data, Template, and Bulk.

I also added a bit of code that will add this script to the top menu in the UI, so it can be run more easily, and added a line that inserts rows to prevent the script from overflowing the size of the spreadsheet.

Answer 2

Try adding flush:

function CopyTemplateToBulkSheet() {
  let ss = SpreadsheetApp.getActive();
  let tsh = ss.getSheetByName('Template-make a copy');
  let trg = tsh.getRange('A2:U7')
  let bsh = ss.getSheetByName('BulkSheet');
  let brg = bsh.getRange(bsh.getLastRow() + 1, 1);
  trg.copyTo(brg, { contentsOnly: true });
}


function Loopthrough() {
  let ss = SpreadsheetApp.getActive();
  let tsh = ss.getSheetByName('Template-make a copy')
  let colH = ss.getRange('H3:H').getValues();
  let targetkeywords = colH.filter(String);
  targetkeywords.forEach(function (value) {
    tsh.getRange('L5').setValue(value)
    SpreadsheetApp.flush();
    CopyTemplateToBulkSheet();
  })
}