google-apps-scriptgoogle-sheets

How to use AppendRow


I took a rather long break from any coding (like 5 years) so now when I'm back to do some code for fun and my own usage and I feel completely lost, especially that I actually never wrote any macro in spreadsheets. So its a challenge for me, especially that I don't take any courses (yet).

This time I was looking into AppendRow function. Part of my code needs simply to reverse order of rows of one sheet and paste it into another. Sorting won't work in this particular case and I could use just CopyTo going from the bottom of first one and copying it from the top in second one. Instead I would like to use AppendRow in second sheet in a loop going from the bottom rows from the first sheet.

Here's my question. How can i use AppendRow to copy content. I was searching for hours and only found a way to use hardcoded appendrow content like:

sheet.appendRow(["a man", "a plan", "panama"]);

I know that appendRow takes Object[][] as parameter but my skills are lacking. I tried to do this like this:

    function SortBankStatement() {
  var ss = SpreadsheetApp.getActive();
  var unsorted_sheet = ss.getSheetByName("RawAcc1");
  var sorted_sheet = ss.getSheetByName("Bank Statement History");
  var unsorted_length = unsorted_sheet.getLastRow();
  var unsorted_width = unsorted_sheet.getLastColumn();
  var unsorted = unsorted_sheet.getRange(1,1,unsorted_length, unsorted_width);
  for (var i=unsorted_length;
       i>1;
       i--) {
    sorted_sheet.appendRow(unsorted[i][0]); //don't work
    sorted_sheet.appendRow([unsorted[1][i]]); //don't work
    sorted_sheet.appendRow([unsorted]); //don't work
    sorted_sheet.appendRow([unsorted[i]]); //don't work
    sorted_sheet.appendRow(unsorted.getValue(i)); //don't work
    sorted_sheet.appendRow(unsorted(please.work)); //surprisingly, don't work

  };
};

Basically I have no idea how I can Append Row using "i" as a row indicator for source. No idea how it should be spelled. All i got are errors about appendRow being a string, that "1" is unspecified and in once case instead of copied data I got all spreadsheet filled with "uspecified" strings or other gibberish.

This code don't have to be optimal, I will look into it after I reach full functionality. Thank you in advance.


Solution

  • AppendRow expects a single row (array) of data or a 1D array.


    Use getRange().getValues(); Range is a object. Values is a array. The loop for: for (var i=unsorted_length; i>1; i--) {}, the first index which is i=unsorted_length is undefined at an array, and the last number of i is 2. Thus, Also, Modify the loop as below:

    function SortBankStatement() {
      var ss = SpreadsheetApp.getActive();
      var unsorted_sheet = ss.getSheetByName("RawAcc1");
      var sorted_sheet = ss.getSheetByName("Bank Statement History");
      var unsorted_length = unsorted_sheet.getLastRow();
      var unsorted_width = unsorted_sheet.getLastColumn();
      var unsorted = unsorted_sheet.getRange(1,1,unsorted_length, unsorted_width).getValues();
      for (var i=unsorted_length - 1; i>1; i--) {
        sorted_sheet.appendRow(unsorted[i]);
        }
    }
    

    Sheet2A1:
    =SORT(Sheet1!A1:E50,ROW(A1:A50),0)
    

    This will order Sheet1A1:E50 Bottom to Top(without any actual sorting).


    Array.reverse()

    var unsortedRng = unsorted_sheet.getRange(1,1,unsorted_length, unsorted_width);
    var unsortedVal = unsortedRng.getValues();
    unsortedVal.reverse();
    unsortedRng.setValues(unsortedVal);