How to populate a column with data after running .map() on 2d Array

Viewed 175

I get the following error when trying to use execute this code. Exception: The number of columns in the data does not match the number of columns in the range. The data has 0 but the range has 1. (line 15, file "Code")

function getName(){
  const srcSheetName = "result";
  const dstSheetName = "Allergy";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const allergyRange = srcSheet.getRange('F1:BQ' + srcSheet.getLastRow()).getValues();
  const dstSheet = ss.getSheetByName(dstSheetName);
  const lr = dstSheet.getRange("A2:A").getLastRow();
  var names = allergyRange.map(row => row.slice(row.length-12)).forEach((row, i) => {
    if(!row.every(val => val == 0)) return allergyRange[i][0]
});
  const dstRange = dstSheet.getRange(2, 1, dstSheet.getLastRow(), 1);
  const dstValues = dstRange.getValues();
  const putValues = [names];
  dstRange.setValues(putValues);
}

I assume this is because the names variable 'names' has too many columns or the desired range has no values. I have tried just putting 'names' directly into the setValues function but this does not work and I get another error message "Bad Value."

UPDATE: This occurs because [names] is empty.

dstRange.setValues(names);

I assume the underlying issue is that I used the .map() method to manipulate the original 2d array instead of Object.assign(), but when I check the literature here it states that .map() creates a new array and JavaScript views arrays as objects.

Trying to diagnose the issue I use Logger.log both inside and outside the loop to get an idea what was happening to the dataset.

Inside loop:

var names = allergyRange.map(row => row.slice(row.length-12)).forEach((row, i) => {
    if(!row.every(val => val == 0)) Logger.log(allergyRange[i][0])
});

Outside loop:

  var names = allergyRange.map(row => row.slice(row.length-12)).forEach((row, i) => {
    if(!row.every(val => val == 0)) return allergyRange[i][0]
});
  Logger.log(names)

When used inside the loop I get the desired list of names but when used outside the loop I get 'null' when checking the logs.

There has to be a critical step I'm missing. How can I take the output from 'names' and use setValues() or something else to place each output in the first column of a sheet in Google Sheets? Is there a way to create a new 2d Array with the output from the loop or should I use another method entirely to manipulate the dataset? I am very new to JavaScript any tips are welcome.

UPDATE:

I tried .reduce() with the following code:

function newNames(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("result");
  var cdata = ss.getSheetByName("Allergy");
  var valuesOfFormData = data.getRange("F1:BQ" + data.getLastRow()).getValues();
  var valuesForOrderChanges = valuesOfFormData.reduce((ar, [c,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ...d]) => {   
    if (d.toString() != 0) ar.push([c]);
    return ar;
  }, []);
  if (valuesForOrderChanges.length > 0) {
    cdata.getRange(1, 1, valuesForOrderChanges.length, valuesForOrderChanges[0].length).setValues(valuesForOrderChanges);
  }
}

I am able to populate column one of the desired sheet, but the list is every name from the array instead of only the names with values in the last 12 columns like in the original code. Currently I can either get the list of all names to the correct column or generate a list of the correct names without the ability to put them in the desired location without an error. What am I missing?

When I made the following adjustment to the original code I got the same error:

  if(!row.every(val => val == 0)) return allergyRange[i][0];
},[]);

I feel like I am getting close or messing things up entirely. How do I reference the result from the .map() loop so I can post it in a column of a google sheet.

UPDATE:

Sample Data Sample Data

Sample Output

Sample Output

Goal: If row in data contains any value in BF1:BQ put the name from column F into column A of allergy. Do this for each row in 'data.

1 Answers

I believe your goal as follows.

  • When there are the values in the cells BF2:BQ in "data" sheet, you want to retrieve the values from the column "F" of the same row, and put to the values to "allergy" sheet.
  • You want to achieve this using Google Apps Script.

In this case, I would like to propose to modify your bottom script at the section of "UPDATE:".

Modified script:

function newNames(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("result");
  var cdata = ss.getSheetByName("result");
  var valuesOfFormData = data.getRange("F2:BQ" + data.getLastRow()).getValues();  // Modified
  var valuesForOrderChanges = valuesOfFormData.reduce((ar, [c,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ...d]) =>{
    if (d.some(e => e.toString() != "")) ar.push([c]);  // Modified
    return ar;
  }, []);
  if (valuesForOrderChanges.length > 0) {
    cdata.getRange(2, 1, valuesForOrderChanges.length, valuesForOrderChanges[0].length).setValues(valuesForOrderChanges);  // Modified
  }
}

Reference:

Related