How to build multi-dimensional arrays in the appropriate orientation. (Rows/Cols in correct places)

Question

I'm working on building a Google Sheets-based tool to calculate the cost of making various machined and fabricated parts. As it currently sits, there are about 60 different variables that I modify each time I build an estimate. Things like "number of parts," "length of bar to cut each part from," "cost/bar," "machining time," "machining rate," etc. All of these values I have populated on one sheet, and laid out in a way like. I want to make a button that takes a "snapshot" of all of these values, and stores them on another sheet for later reference. I'd then, ideally create another button, that allows me to re-populate all of the cells based off of a unique ID (such as Part #). This would let me tweak an estimate, or even refer back to material sizes etc in a meaningful way.

So far, I've created a "Named Range" for each of the values, so that as I change the layout, or add values, my script code should update accordingly, instead of using direct cell references.

I've built a few functions to get and set the value's of these named ranges. They're working as expected(i think) for what I'm trying to do. But when I try to place the array of Named Ranges inside of a multi-dimensional array of the named ranges WITH their respective values, I'm running into an issue where each named range is a ROW and their respective value is a second Column. And I need it swapped

I'm not super comfortable with multi-dimensional arrays and am thinking myself in circles trying to figure out how to transpose this logically. My gut says the way I'm attempting to build the arrays is my problem, not just how I'm iterating through them.

function saveCurrentValues(){
 //set master spreadhseet
 var ss = SpreadsheetApp.getActiveSpreadsheet(); 

 //set calc and save sheets to vars  
 var calcSheet = ss.getSheetByName('Part Cost Calculator')
 var saveSheet = ss.getSheetByName('Saved Parts');

 //set named ranges from calcSheet to array
 var namedRanges = calcSheet.getNamedRanges();
 var savedValues = new Array();

 //find next available row for save data (currently troubleshooting)
 var nextAvailSaveRange = saveSheet.getRange(1, 1, 60, 2);

 //iterate through array and call getNamedRange() function to return name and current value
 for(i = 0; i < namedRanges.length; i++){
  savedValues[i] = getNamedRange(namedRanges[i].getName());
 }
 nextAvailSaveRange.setValues(savedValues); 
}

function getNamedRange(name){
 var ss = SpreadsheetApp.getActiveSheet();
 var value = ss.getRange(name).getValue();
 Logger.log([name,value]);
 return [name, value];
}

As you can see by how I had to temporarily format the nextAvailSaveRange, it needs 60 ROWS, and only two columns, because of how the array is constructed. I'd like to better understand how I'm creating this multi-dimensional array vertically instead of horizontally, and how to fix it!

Once this is done, I'd like to create headers that match the Named Ranges on my save sheet, to allow me to iterate through functions and look for a match to the appropriate column by name. That way if I add more values or change their order, or the order of the array, it wont matter. I think I'll be able to figure that out pretty easily if I can control these damn arrays better!

Answer 1

I agree with the OP. Array building AND iteration are the immediate problems and they are the stumbling block to the development of the spreadsheet.

The OP has a raised number of issues, however the most immediate, and the one to be resolved under this answer, is the copying of a list of parts from one sheet to another. In the OP's code, named ranges were retrieved and used as a basis for creating the copy of the list of parts. However, this also creates a duplicate set of named ranges on the target sheet. In my view this was unnecessarily complicating the duplication of the parts list since it is easy to programmatically create/update a list of named ranges.

The following code consists of three functions:

• so_5466573501() - Copies the list of parts from one sheet to another.
  Named Ranges are ignored; the OP's stumbling block is the iteration of the raw data and management of arrays. This code deals only with that aspect as a means of simplifying this issue.

• createnamedranges() - Programmatically creates/updates Named ranges.
  This code is included to assure the OP that it is not important to make named ranges the focus of the duplication by showing how easy it is to programmatically turn a list of parts into a series of Named Ranges (for development, I created 60 Parts and the entire code executes in under a 1 second). The code assumes a list in two columns (Column A = Parameter Name, Column B = Parameter value). The code loops through the list creating/updating a set of named ranges - the range name is the Parameter Name in Column A, and the range itself is the the corresponding row in Column B. The name of the sheet is set in a variable, so this function can be easily adapted.

• deletenamedranges() - Programmatically deletes Named ranges.
  This code deletes all the Named Ranges from a given sheet. This function is included because the OP's existing code creates duplicate named ranges, and it might be necessary to quickly delete them from a sheet. The sheet name is stored as a variable, so the function can be easily adapted.

---

function so_5466573501() {

  //set master spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //create variables for calc and save sheets
  var calcSheet = ss.getSheetByName('Part Cost Calculator')
  var saveSheet = ss.getSheetByName('Saved Parts');

  //get the Parts Parameters from Part Cost Calculator
  //var namedRanges = calcSheet.getNamedRanges();
  //Logger.log("DEBUG: Number of named ranges on Parts Cost Calculator = "+namedRanges.length);

  // get the number of parts in the list on Parts Cost Calculator
  var Avals = calcSheet.getRange("A1:A").getValues();
  var Alast = Avals.filter(String).length;
  //Logger.log("DEBUG: Number of parts in the list: "+Alast); //DEBUG

  // get the parts list
  var partsRange = calcSheet.getRange(1, 1, Alast, 2);
  var partsRangeValues = partsRange.getValues();
  //Logger.log("DEBUG: The parts range is: "+partsRange.getA1Notation());//DEBUG
  //Logger.log("DEBUG: Parts List Row #1: Name: "+partsRangeValues[0][0]+", Value: "+partsRangeValues[0][1]);//DEBUG


  // create an array to use for saving results and updating new Saved Parts sheet
  var savedValues = new Array();

  // Loop through the Parts List, row by row
  for (i = 0; i < Alast; i++) {

    // push the part name and part value onto the array 
    savedValues.push([partsRangeValues[i][0], partsRangeValues[i][1]]);
    //Logger.log("DEBUG: Parts List: i = "+i+", Name: "+partsRangeValues[i][0]+", Value: "+partsRangeValues[i][1]);//DEBUG

  }

  // identify the range on the Saved Parts sheet to copy the parts list array.
  var saveRange = saveSheet.getRange(1, 1, Alast, 2);
  saveRange.setValues(savedValues);
}

---

 function createnamedranges() {

  //set master spreadhseet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //create variables for calc and save sheets
  var calcSheetName = "Part Cost Calculator";
  var calcSheet = ss.getSheetByName(calcSheetName);

  // get the number of parts in the list on Parts Cost Calculator
  var AVals = calcSheet.getRange("A1:A").getValues();
  var ALast = AVals.filter(String).length;

  // get the parts range and values
  var partsRange = calcSheet.getRange(1, 1, ALast, 2);
  //Logger.log("DEBUG: The Parts range is "+partsRange.getA1Notation());//DEBUG
  var partsRangeValues = partsRange.getValues();

  // Loop through the parts list row by row
  for (var i = 0; i < ALast; i++) {

    // get the Part name and assign as the range name
    var nrpartname = partsRangeValues[i][0];
    //Logger.log("DEBUG: PartName = "+nrpartname+", value: "+partsRangeValues[i][1]);//DEBUG

    // get the range to be named -note (i+1) because the loop starts at 0 (zero) but `getrange` starts at 1 (one)
    var rng_to_name = ss.getSheetByName(calcSheetName).getRange((i + 1), 2);
    //Logger.log("DEBUG: rng_to_name: "+rng_to_name+", range details: "+rng_to_name.getA1Notation());

    // set (and/or update) the named range
    ss.setNamedRange(nrpartname, rng_to_name);

    // DEBUG: check that the range was created //DEBUG
    // var rangeCheck = ss.getRangeByName(nrpartname);//DEBUG
    // var rangeCheckName = rangeCheck.getA1Notation(); //DEBUG
    // Logger.log("DEBUG: Rangename: "+nrpartname+", Range: "+rangeCheckName);//DEBUG
    // credit megabyte1024 https://stackoverflow.com/a/12325103/1330560 "setNamedRange() outside of the spreadsheet container?"

  }

}

---

function deletenamedranges() {

  //set master spreadhseet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //create variables for calc and save sheets
  var calcSheet = ss.getSheetByName('Part Cost Calculator');

  // get the named ranges
  var namedRanges = calcSheet.getNamedRanges();

  // loop through the list of named ranges and delete them
  for (var i = 0; i < namedRanges.length; i++) {
    namedRanges[i].remove();
  }
}

---

ADDENDUM: - Copy based on Named Ranges

The original so_5466573501 assumes that the parts are in a simple 2 column-list; in which case, Named Ranges are irrelevant.

The following code assumes that the parts are not in a list but scattered, in no particular order, throughout the sheet "Part Cost Calculator". This code is based on obtaining the NamedRanges, identifying the respective Named Range row and column, correlating said row and column to the ENTIRE data range, and then copying the results to the "Saved Parts" sheet. No Named Ranges are created by default on the "Saved Parts" sheet but this can be easily done by using the createnamedranges function (appropriately edited for the correct sheet name).

function so_5466573502() {

  //set master spreadhseet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //create variables for calc and save sheets
  var calcSheet = ss.getSheetByName('Part Cost Calculator')
  var saveSheet = ss.getSheetByName('Saved Parts');

  //get the Parts Parameters from Part Cost Calculator
  var namedRanges = calcSheet.getNamedRanges();
  var numNR = namedRanges.length
  //Logger.log("DEBUG: Number of named ranges on Parts Cost Calculator = "+numNR);

  // get all the data
  var dataRangeValues = calcSheet.getDataRange().getValues();

  // create an array to temporarily store results
  var resultsarray = [];

  // Loop through the array of Named Ranges
  for (var x = 0; x < numNR; x++) {

    var nrName = namedRanges[x].getName();
    var nrRange = namedRanges[x].getRange();
    var nrRangerow = nrRange.getRow();
    var nrRangecol = nrRange.getColumn();
    var nrRangeValue = dataRangeValues[nrRangerow - 1][nrRangecol - 1];
    //Logger.log("DEBUG: Named Range-Name: "+nrName+", Range: "+nrRange.getA1Notation()+", Row: "+nrRangerow+", Column: "+nrRangecol+", Value-"+nrRangeValue);//DEBUG

    // populate the array with the part name and the part value
    resultsarray.push([nrName, nrRangeValue]);
  }

  // identify the range on the Saved Parts sheet to copy the parts list array.
  var saveRange = saveSheet.getRange(1, 1, numNR, 2);
  saveRange.setValues(resultsarray);

  // sort the results  on "Saved Parts"
  saveRange.activate().sort({
    column: 1,
    ascending: true
  });
}