CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
iEatBabyWookiees
iEatBabyWookiees

Reputation: 3

Google Sheets Script for loop no longer pasting until after the script has ended or cancelled

For some reason, my code that was working as of yesterday has quit working today. I can't seem to identify what I did to cause the issue and could use some more eyes on it. It was a really simple process but I am extremely new to the language, so I'm sure I'm missing something.

The issue is that the for loop used to update/paste to cell B2 on the sheet, but now it doesn't do that until after I cancel the code or it ends (using whatever the most recent value for num was). It's causing the information to not be updated and so all I get is the information associated with whatever is in cell B2 pasted all the way down through the end of the rows. It used to wait until B2 was updated before copying and pasting to the next row, which took a while but I still had more than enough time before the 5 minute limit since I'm not running a ton of data. My only thought is that I must've made a minor edit that changed this, but I can't track it down in version history. Any help would be greatly appreciated.

 function CopyandPasteNewData2() {

   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sh = ss.getSheetByName('sheet1');
   var lastRow = sh.getLastRow();
   var num = 6;
   
   for(var i = 6; i<=lastRow; i++)
   {
   
   num;
     
   sh.getRange('A'+num).copyTo(sh.getRange('B2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
   sh.getRange('D2:H2').copyTo(sh.getRange('B'+num+':F'+num), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
   
   num++;
     
   }
   
 };

Upvotes: 0

Views: 72

Answers (1)

Marios
Marios

Reputation: 27348

When you iteratively modify the values of a sheet it is always a good practice to use flush() to apply all pending Spreadsheet changes.

Please try the following small modification:

function CopyandPasteNewData2() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('sheet1');
  var lastRow = sh.getLastRow();
  var num = 6;
  
  for(var i = 6; i<=lastRow; i++)
  {
  
  num;
    
  sh.getRange('A'+num).copyTo(sh.getRange('B2'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
  sh.getRange('D2:H2').copyTo(sh.getRange('B'+num+':F'+num), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
  SpreadsheetApp.flush(); // <- new code
  
  num++;
    
  }
  
};

Upvotes: 1

Related Questions