CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
lise
lise

Reputation: 163

Google Sheets Script - This script to copy columns works, but is it bad form?

This script works (and I'm quite proud of myself for having created my first script!), but I worry it could be bad form to have two copy/paste type functions especially considering they share a common set of columns. There will be a lot of data at one point (3,000 rows, 38 cols). Should there be a pause in between? Does the script finish the first function before starting the 2nd? Am I allowed to have two functions? Thanks for the help.

//Copy 3 cols with original data into a backup range.
//Copy the 3 update cols to the original cols as values only

function testReplace_Orig_Review_tag_Rating_Cols_with_Updates () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = ss.getSheetByName('Sheet1');
  var backup = sheet.getRange(3, 35, ss.getLastRow()-3, 3); // backup of original reviews, tags, ratings before they get overwritten 
  var update = sheet.getRange(3, 32, ss.getLastRow(), 3);  //updated reviews, tags, ratings
  var original = sheet.getRange(3, 13, ss.getLastRow()-3, 3); //original reviews, tags, ratings

original.copyTo(backup,SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
update.copyTo(original,SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    
}

Background: I do an importxml on one sheet, and I copy/paste as values on another. In this values only page I have to update 3 cols. I added 3 cols at the end of my sheet where I do an importxml if the original cols are empty. But of course once those cols get updated I have to paste the data into the original cols as values only. Before I mess about with all of this I want to backukp those 3 original cols before they get overwritten. That's what my script does. It backups up the original cols, then pastes the updated cols into the original ones.

Upvotes: 0

Views: 52

Answers (1)

Cooper
Cooper

Reputation: 64040

Try this:

function testReplace_Orig_Review_tag_Rating_Cols_with_Updates () {
  var ss = SpreadsheetApp.getActive();  
  var sheet = ss.getSheetByName('Sheet1');
  var backup = sheet.getRange(3,35,sheet.getLastRow()-2,3);
  var update = sheet.getRange(3,32,sheet.getLastRow()-2,3);  
  var original = sheet.getRange(3,13,sheet.getLastRow()-2,3);
  original.copyTo(backup,SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  SpreadsheetApp.flush();
  update.copyTo(original,SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    
}

Upvotes: 1

Related Questions