2

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.

7
  • 1
    Can I ask you about your sample input values of allergyRange and the sample output values you expect? Because in your script, at row.slice(row.length-12)), the cells BF1:BQ are retrieved. In this case, I thought that the values might be able to be directly retrieved from the cells BF1:BQ. But from your question, I cannot understand about your goal. So I would like to ask the sample input and output values. Commented Nov 24, 2020 at 22:43
  • updated. Please let me know if you need me to add more information. Commented Nov 24, 2020 at 22:59
  • 1
    Thank you for replying and adding more information. But I have to apologize for my poor understanding. Unfortunately, from your updated question, I couldn't understand about your goal. By this, I cannot understand about your current issue of your script. I apologize for this. For example, can you provide the sample input and output values you expect? By this, I would like to try to understand it. Commented Nov 24, 2020 at 23:11
  • @Tanaike I have provided updated examples and description. Please let me know if you need more information. Commented Nov 24, 2020 at 23:25
  • 1
    Thank you for replying and adding more information. I would like to confirm my understanding of your goal. In your goal, 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. Is my understanding correct? Commented Nov 24, 2020 at 23:30

1 Answer 1

2

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:

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.