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 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.


allergyRangeand the sample output values you expect? Because in your script, atrow.slice(row.length-12)), the cellsBF1:BQare retrieved. In this case, I thought that the values might be able to be directly retrieved from the cellsBF1:BQ. But from your question, I cannot understand about your goal. So I would like to ask the sample input and output values.BF2:BQin "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. Is my understanding correct?