Is there a way to iterate down rows and across columns and compile that data into a cell?

Question

I am trying to compile data from one sheet containing order information to another sheets cell based on each customers name. I'd like to take the title of the column as well as the count for the item(s) they have ordered. I tried to brute force it with a formula in Google Sheets but the formula messes up and stops giving the correct information so I figured using a scripts function would be better for what I am trying to do. I made a new sheet to test a script on but have little experience and can't seem to make any progress.

I'd like to get the title(top row) of each column and the count of the item(s) into the order column on sheet2 base on the matching names on both sheets. If anyone could help or provide some insight it would be greatly appreciated.

Here is the code I came up with:

 function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var orders = ss.getSheetByName("Sheet2");
  var data = ss.getSheetByName("Sheet1");
  var names = orders.getRange("Sheet2!A2:A5");
  var name = names.getValues();
  var startRow = 1
var endRow = ss.getLastRow()
var getRange = ss.getDataRange();
  var getRow = getRange.getRow();
  var title = data.getRange("Sheet1!B1:J1");
  var item = title.getValues();
  var itemCell = data.getRange("Sheet1!B2").getValue();
  var orderCell = orders.getRange(2,2).getValue()
  
  Logger.log(itemCell)
   
  if(itemCell>=0){
    orders.getRange(2,2).setValue("item1 x " +itemCell)
  }

currently this only has the desired effect on one cell and does not complete the row and repeat on next column.

Here is how I adjusted the code to try and fit a larger data set:

function myFunction() {
  const srcSheetName = "Test Data";
  const dstSheetName = "Order Changes";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Retrieve source values.
  const srcSheet = ss.getSheetByName(srcSheetName);
  //I changed values  1,1 to change the values that were retreived
  const [[, ...header], ...srcValues] = srcSheet.getRange(1, 1, 
  srcSheet.getLastRow(), srcSheet.getLastColumn()).getValues();
  
  // 2. Create an object using the source values.
  const srcObj = srcValues.reduce((o, [a, ...v]) => {
    const temp = v.reduce((s, r, i) => {
      if (r.toString() != "") s += `${header[i]} ${r}`;
      return s;
    }, "");
    return Object.assign(o, {[a]: temp || ""});
  }, {});
  
  // 3. Retrieve the header column of destination values.
  const dstSheet = ss.getSheetByName(dstSheetName);
  //I changed values 2 or 1 to adjust the destination of values
  const dstRange = dstSheet.getRange(2, 1, dstSheet.getLastRow() - 1);
  const dstValues = dstRange.getValues();
  
  // 4. Create the output values using the header column and the object.
  const putValues = dstValues.map(([a]) => [srcObj[a] || ""]);
  
  // 5. Put the values.
  dstRange.offset(0, 1).setValues(putValues);
}

after making the changes and running the code the values would either not appear or appear in the wrong column with incorrect data.

Goal of function Update:

Match names in Sheet2!A with names in Sheet1!F
If a match is found combine header and value of cells in Sheet1!N1:BQ. This should be from the same row of the matched name in Sheet1!F (example: John Smith lm14 1, lm25 2, lm36 1)
Place combined data into Sheet2!C
Repeat for every name in Sheet2!A

header and cell value should not be combined if value < 0

I hope this helps to clarify any misunderstanding.

Here is are better example images:

Tanaike Tanaike · Accepted Answer · 2020-11-17T23:05:52

I believe your goal as follows.

In your goal, the upper image in your question is the output you expect, and the cells "B2" should be item1 1 item3 1 item6 2 item9 3 when the lower input situation is used.
You want to achieve this using Google Apps Script.

In order to achieve above, I would like to propose the following flow.

Retrieve source values.
Create an object using the source values.
Retrieve the header column of destination values.
Create the output values using the header column and the object.
Put the values.

Sample script:

Please copy and paste the following script to the script editor of the Spreadsheet and set the source sheet name and destination sheet name, and run myFunction.

function myFunction() {
  const srcSheetName = "Sheet1";
  const dstSheetName = "Sheet2";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Retrieve source values.
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [[, ...header], ...srcValues] = srcSheet.getRange(1, 1, srcSheet.getLastRow(), srcSheet.getLastColumn()).getValues();
  
  // 2. Create an object using the source values.
  const srcObj = srcValues.reduce((o, [a, ...v]) => {
    const temp = v.reduce((s, r, i) => {
      if (r.toString() != "") s += `${header[i]} ${r}`;
      return s;
    }, "");
    return Object.assign(o, {[a]: temp || ""});
  }, {});
  
  // 3. Retrieve the header column of destination values.
  const dstSheet = ss.getSheetByName(dstSheetName);
  const dstRange = dstSheet.getRange(2, 1, dstSheet.getLastRow() - 1);
  const dstValues = dstRange.getValues();
  
  // 4. Create the output values using the header column and the object.
  const putValues = dstValues.map(([a]) => [srcObj[a] || ""]);
  
  // 5. Put the values.
  dstRange.offset(0, 1).setValues(putValues);
}

References:

Added 1:

About your current issue, the reason of your issue is that the ranges of source and destination are the different from the sample image in your question. My suggested answer is for the sample images in your initial question. When you changed the structure of the Spreadsheet, it is required to modify my suggested script. But from I changed values 1,1 to change the values that were retrieved and I changed values 2 or 1 to adjust the destination of values, I couldn't understand about your modified script. So as the additional script, I would like to modify my suggested answer for your updated question.

From your updated question and replyings, I understood that the source range and destination range are "N1:BQ" and "A52:C79", respectively. From this, please modify above sample script as follows.

From:

const [[, ...header], ...srcValues] = srcSheet.getRange(1, 1, srcSheet.getLastRow(), srcSheet.getLastColumn()).getValues();

To:

const [[, ...header], ...srcValues] = srcSheet.getRange("N1:BQ" + srcSheet.getLastRow()).getValues();

and

From:

dstRange.offset(0, 1).setValues(putValues);

To:

dstRange.offset(0, 2).setValues(putValues);

Added 2:

About your current issue, the reason of your issue is that the ranges of source and destination are the different from your 1st updated question in your question. My suggested answer is for the sample images in your 1st updated question. When you changed the structure of the Spreadsheet, it is required to modify my suggested script.

From your 2nd updated question, I understood that the source range and destination range are "F1:BQ" (the column "F" is the title and the columns "N1:BQ" are the values.) and "A2:C", respectively. From this, please modify above sample script as follows.

function myFunction() {
  const srcSheetName = "Sheet1";
  const dstSheetName = "Sheet2";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Retrieve source values.
  const srcSheet = ss.getSheetByName(srcSheetName);
  const [[,,,,,,,, ...header], ...srcValues] = srcSheet.getRange("F1:BQ" + srcSheet.getLastRow()).getValues();

  // 2. Create an object using the source values.
  const srcObj = srcValues.reduce((o, [a,,,,,,,, ...v]) => {
    const temp = v.reduce((s, r, i) => {
      if (r.toString() != "") s += `${header[i]} ${r}`;
      return s;
    }, "");
    return Object.assign(o, {[a]: temp || ""});
  }, {});
  
  // 3. Retrieve the header column of destination values.
  const dstSheet = ss.getSheetByName(dstSheetName);
  const dstRange = dstSheet.getRange(2, 1, dstSheet.getLastRow() - 1);
  const dstValues = dstRange.getValues();
  
  // 4. Create the output values using the header column and the object.
  const putValues = dstValues.map(([a]) => [srcObj[a] || ""]);

  console.log(srcObj)

  // 5. Put the values.
  dstRange.offset(0, 2).setValues(putValues);
}

Is there a way to iterate down rows and across columns and compile that data into a cell?

1 Answers

Sample script:

References:

Added 1:

From:

To:

From:

To:

Added 2: