CODEWITHSUNDEEP
javascriptgoogle-apps-script
JLBJones
JLBJones

Reputation: 119

How do I reorder and array and setValues of the newly ordered array into my spreadsheet?

I'm pulling data from one sheet as an array and then putting it into anther sheet, but the values need to be in a different order.

If the source array searchData where [1,2,3,4,5,6,7] I need to place the values pastearray into the second sheet in the order [7,1,7,4,3,0,2,7,5]. I have three problems, one is that the order is changed, the second is that there are values repeated in the pastearray (namely the 7th value), and the third is that several of the values pulled from searchData are null/blank, in this case, the 7s.

I am doing this for a class project. I am just learning about arrays and still having trouble completely grasping how to manipulate them. I have already tried 1D, 2D, 3D conversion, at least I think that is what I was doing...not sure if I was correct. I have verified that everything else in the script works except for the final pasteRange.setValues(pastearray[0]);.

My log shows that pastearray contains:

[[null], [Language Arts 6], [null], [A/1], [6.0], [67511.0], [Layton, B], [[email protected]]]

I also tried:

["Language Arts 6", "A/1", "6.0", "67511.0", "Layton, B", "[email protected]"]

I've tried removing the "null" values or leaving them in. I've tried straight grabbing a row of values and setting them, and that worked fine as long as I don't try to re-arrange them.

function onEdit(e){
  if(e.range.getA1Notation() == 'F3' && 
      e.range.getSheet().getName() == 'Class Information and Roster') {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var paste2 = ss.getSheetByName("Class Information and Roster");
    var tclimport = ss.getSheetByName("TeacherClassListImport");

    var classID = e.range.getValue();//Get class id value

    Logger.log(classID);

    paste2.getRange(6,1,31,10).clearContent();//clear all old info just in case

    //access the list of all classes


    var lr = tclimport.getLastRow();
    var searchData = tclimport.getRange(1,1,lr,7).getValues(); //make an array of all the classes available

    //Logger.log(searchData);

    //Find the row with that class ID
    for (var i = 0; i < searchData.length; i++) {
      if(searchData[i][0]==classID){
        Logger.log(i+1);
        //Logger.log(searchData);


        var pastearray = [[searchData[i][7]],[searchData[i][1]], [searchData[i][7]], [searchData[i][4]], [searchData[i][3]], [searchData[i][0]], [searchData[i][2]], [searchData[i][7]], [searchData[i][5]]];
        Logger.log(pastearray);

      //Copy in info from class ID list
        var pasteRange = paste2.getRange(3,1,1,9);

        pasteRange.setValues(pastearray); 
         }
    }
  }
}

I'm sure you will cringe looking at my code since it is so rudimentary, but it works the way I understand...all except the final setValues().

The idea of this code is that you enter a class ID number in cell F3. OnEdit triggers the script to find the details about that class in the sheet "TeacherClassListImport", copy those values and returns the values in row 3 in a different order class name, time period, level, class ID, teacher name, and teacher email. It will overwrite the same cell F3 with the same class ID unless there is a better way to do it. The 7th is a null/blank cell. I'm not sure that it even has to be there except for proper spacing to fit the template.

Any help is appreciated greatly.

Upvotes: 1

Views: 114

Answers (1)

dwmorrin
dwmorrin

Reputation: 2734

Your pasteRange is 1 row long and 9 columns wide. Your pastearray is 9 rows long and 1 column wide.

You have 

var pastearray = [
  [searchData[i][7]], [searchData[i][1]], [searchData[i][7]],
  [searchData[i][4]], [searchData[i][3]], [searchData[i][0]],
  [searchData[i][2]], [searchData[i][7]], [searchData[i][5]]
];

but your range wants

var pastearray = [[
  searchData[i][7], searchData[i][1], searchData[i][7],
  searchData[i][4], searchData[i][3], searchData[i][0],
  searchData[i][2], searchData[i][7], searchData[i][5]
]];

Upvotes: 2

Related Questions