Country data archiving script using Google Spreadsheet

Question

I've just created a script in Google Spreadsheet in order to archive some things. I have data from four countries. If something is marked done, then that line could be archived into that country's archive.

My code is pretty ugly. Is there a clearer way to do this?

function CroatianArchive() 
{  
  var source_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('original');
  var target_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('croatian_backup');
  var lastRow = source_sheet.getLastRow();
  var source_range = source_sheet.getDataRange();
  var target_range = target_sheet.getDataRange();  
  var rowsToBeDeleted = [];

  var i = 2;
  while (i <= lastRow) {
  if (source_sheet.getRange("A"+i).getValue() == "CRO" && source_sheet.getRange("M"+i).getValue() == "DONE" ) {
    var office = source_sheet.getRange("A"+i).getValue();
    var title = source_sheet.getRange("B"+i).getValue();
    var imdbId = source_sheet.getRange("C"+i).getValue();
    var channel = source_sheet.getRange("D"+i).getValue();
    var type = source_sheet.getRange("E"+i).getValue();
    var added = source_sheet.getRange("F"+i).getValue();
    var deadline = source_sheet.getRange("G"+i).getValue();
    var airing = source_sheet.getRange("H"+i).getValue();
    var link = source_sheet.getRange("I"+i).getValue();
    var picture = source_sheet.getRange("J"+i).getValue();
    var comment = source_sheet.getRange("K"+i).getValue();
    var portId = source_sheet.getRange("L"+i).getValue();
    var status = source_sheet.getRange("M"+i).getValue();
    var data = [office,title,imdbId,channel,type,added,deadline,airing,link,picture,comment,portId,status];
    target_sheet.appendRow(data);    
    rowsToBeDeleted.push(i);
    i++;
    } else {
      i++;
      }
    }  
    rowsToBeDeleted.reverse();
    for (var j = 0; j < rowsToBeDeleted.length; j++) 
    {
        source_sheet.deleteRow(rowsToBeDeleted[j]);
    }   
    SpreadsheetApp.getActiveSpreadsheet().toast('Archivation finished.', 'Status');
  }
function SerbianArchive() 
{  
  var source_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('original');
  var target_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('serbian_backup');
  var lastRow = source_sheet.getLastRow();
  var source_range = source_sheet.getDataRange();
  var target_range = target_sheet.getDataRange();  
  var rowsToBeDeleted = [];

  var i = 2;
  while (i <= lastRow) {
  if (source_sheet.getRange("A"+i).getValue() == "SER" && source_sheet.getRange("M"+i).getValue() == "DONE" ) {
    var office = source_sheet.getRange("A"+i).getValue();
    var title = source_sheet.getRange("B"+i).getValue();
    var imdbId = source_sheet.getRange("C"+i).getValue();
    var channel = source_sheet.getRange("D"+i).getValue();
    var type = source_sheet.getRange("E"+i).getValue();
    var added = source_sheet.getRange("F"+i).getValue();
    var deadline = source_sheet.getRange("G"+i).getValue();
    var airing = source_sheet.getRange("H"+i).getValue();
    var link = source_sheet.getRange("I"+i).getValue();
    var picture = source_sheet.getRange("J"+i).getValue();
    var comment = source_sheet.getRange("K"+i).getValue();
    var portId = source_sheet.getRange("L"+i).getValue();
    var status = source_sheet.getRange("M"+i).getValue();
    var data = [office,title,imdbId,channel,type,added,deadline,airing,link,picture,comment,portId,status];
    target_sheet.appendRow(data);    
    rowsToBeDeleted.push(i);
    i++;
    } else {
      i++;
      }
    }  
    rowsToBeDeleted.reverse();
    for (var j = 0; j < rowsToBeDeleted.length; j++) 
    {
        source_sheet.deleteRow(rowsToBeDeleted[j]);
    }   
    SpreadsheetApp.getActiveSpreadsheet().toast('Archivation finished.', 'Status');
  }

Answer 1

You have a lot of repeated code when creating the data array. Note that the names of each cell value are ultimately irrelevant. That part could also be expressed as:

var columns = ["A" // office
              ,"B" // title
              ,"C" // imdbId
              ,"D" // channel
              ,"E" // type
              ,"F" // added
              ,"G" // deadline
              ,"H" // airing
              ,"I" // link
              ,"J" // picture
              ,"K" // comment
              ,"L" // portId
              ,"M" // status
              ];
var data = columns.map(function (col) {
  return source_sheet.getRange(col + i).getValue();
});

Less redundant code, same level of documentation.

Code like

if (...) {
  ...;
  i++;
} else {
  i++;
}

is usually better written as:

if (...) {
  ...;
}
i++;

However, we now have

var i = 2;
while (i <= lastRow) {
  ...;
  i++;
}

which is a more complicated formulation of

for (var i = 2; i <= lastRow; i++) {
  ...;
}

Similarly,

rowsToBeDeleted.reverse();
for (var j = 0; j < rowsToBeDeleted.length; j++) 
{
    source_sheet.deleteRow(rowsToBeDeleted[j]);
}

is the same as:

for (var i = rowsToBeDeleted.length - 1; i >= 0; i--) {
  source_sheet.deleteRow(rowsToBeDeleted[i]);
}

If I see that correctly, your two function only differ in the strings croatian_backup vs. serbian_backup and CRO vs. SER. Instead of copy-pasting that code, take those strings from the function parameters. Now we have a general archive function:

function archive(outputSheet, languageCode) { ... }

If you need two seperate funtions that don't take any arguments, we can use the currying technique (also known as partial application) to pre-fill the arguments:

function CroatianArchive() {
  return archive("croatian_backup", "CRO");
}

Some of your names use underscores, other capitalization to separate words:

source_range
lastRow

You should settle for one style – Javascript tends to prefer capitalization rather than underscores.