How to iterate through a range, get 1st Col data once and continue iterating to form an html table, using Google Apps Script?

This is not duplicated, as the result goes into a string, not an HTML file.

The code below almost gets me there, but it keeps repeating the first column’s data, while it should appear in the result only once:

The data:

City	Unit	item	Qty
Oregon	Norman	Item A	10
Oregon	Alex	Item B	50
Seattle	Monica	Item A	10

The result shows Oregon twice and the rest of Oregon related data, while it should appear once and the related data underneath it and then the loop goes on to the next City. I’m not sure if this should be handled during the loop, or if this should be trated in the result itself. This will be an html string to be pasted into a cell.

Here’s the code, as it is now:

function dataToHTML() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  for (let s in allSheets){
    let sheet = allSheets[s]
    if (sheet.getName() == "State1"){
      
      let html = '';
      let location = sheet.getRange(1,1).getValue();
      let state = sheet.getRange(1,2).getValue();
      let locationHTML = '<h1>' + location + ' Businesses' + '</h1>' + 'n';
    
      html += locationHTML

      let dataValues = sheet.getRange(4, 1, sheet.getLastRow(), 6).getValues();

      for (let a = 0; a < dataValues.length; a++){
        if (dataValues[a][0] != ''){
          let city = '<h2>' + dataValues[a][0] + '</h2>' + "n";
          let unit = '<h3><a href="' + dataValues[a][2] + '" target="_blank">' + dataValues[a][1] + '</a></h3>' + "n";
          let item= '<a href="tel: ' + dataValues[a][5] + '</a>' + 'n';
          let qty= dataValues[a][3] + ', ' + state + ' ' + dataValues[a][4] + "n";
          html += city + unit + item + qty
        }
      }      
      ss.getSheetByName('Sheet5').getRange(1,1).setValue(html);
    }
  }  
}

Expected output:

"<h1>Georgia Businesses</h1>
<h2>Oregon</h2>
<h3><a href=""Item A"" target=""_blank"">Norman</a></h3>
<a href=""tel: </a>
10, GA 
<h3><a href=""Item B"" target=""_blank"">Alex</a></h3>
<a href=""tel: </a>
50, GA 
<h2>Seattle</h2>
<h3><a href=""Item A"" target=""_blank"">Monica</a></h3>
<a href=""tel: </a>
10, GA "

Oregon should show once.

As usual, appreciate your help.

Answer

I believe your goal is as follows.

• You want to convert your sample data in your question to the value of “Expected output:” in your question.

When I saw your sample data and script, I noticed the following points.

• It seems that location and state are the cells “A1” and “B1”. In your sample data, the cells “A1” and “B1” are “City” and “Unit”, respectively. But in your expected result, Georgia and GA are used as location and state, respectively.
• In your sample data, there are 4 columns (A – D). But in your script, it seems that the column “F” is used like dataValues[a][5].

In this answer, I would like to propose a modified script by supposing that your sample data has the columns “A” to “F” and the values of cells “A1” and “B1” is the header values of your actual sheet.

When your script is modified for achieving the goal, how about the following modification?

Modified script:

function dataToHTML() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("State1");
  if (!sheet) return;
  const [[location, state], ...values] = sheet.getDataRange().getValues();
  const obj = values.reduce((m, [a, ...b]) => m.set(a, m.has(a) ? [...m.get(a), b] : [b]), new Map());
  let html = `<h1>${location} Businesses</h1>n`;
  obj.forEach((v, k) => {
    html += `<h2>${k}</h2>n`;
    v.forEach(e => {
      html += `<h3><a href="${e[1]}" target="_blank">${e[0]}</a></h3>n`;
      html += `<a href="tel:${e[4]}">${e[4]}</a>n`;
      html += `${e[2]}, ${state} ${e[3]}n`;
    });
  });
  ss.getSheetByName('Sheet5').getRange(1, 1).setValue(html);
}

• In your script,
  • When you want to use only the sheet “State1”, you can directly retrieve it using getSheetByName.
  • getValue() is used in the loop. In this case, the process cost will become high.
  • About let item= '<a href="tel: ' + dataValues[a][5] + '</a>' + 'n';, the value of tel: is not enclosed by the double quotes.
• In this modification,
  1. Values are retrieved from the sheet “State1”.
  2. Create an object for putting to HTML.
  3. Create the HTML data.

Note:

• I thought that the provided sample data in your question might not be for your expected result. So I proposed the above-modified script by supposing your sample data. So, when the above script was not the result you expect, can you provide the sample input and output data you expect? By this, I would like to modify the script.

References:

• getSheetByName(name)
• Map