Thiết kế website giá rẻ

Question

I have tried my best, but I cannot get Google Apps Script to recognize and display my images from the following Google Sheet:
https://docs.google.com/spreadsheets/d/1q4qRgXSq6xXhddArPCoBIuOOupRvokUd0andADCNN0Q/edit#gid=1708987569

The page looks like this:

The App Script I am using is found here:
https://script.google.com/home/projects/19s4gBBgyIoNxfizdlopdsPBTgVAQ_bvqVZJZ6opWuSfrPsFXAnLEdk96/edit

It represents the table decently well and allows someone to add data and submit it in field D2 & D3 because I had a search box in there (which I removed for simplicity’s sake). But it does not show the images.

Can you help me figure out how to show images on the fly? This will be the results page from a Search, so it needs to sense whatever image is there and show it. I am open to coding the images in a different way in the sheet to make them more accessible. I’m basically willing to do almost anything to make this work dynamically.

Here is the code:

const sheetId = "1q4qRgXSq6xXhddArPCoBIuOOupRvokUd0andADCNN0Q";
const sheetName = "MAIN";

function doGet() {
  return HtmlService.createHtmlOutput(getHtml());
}

function getHtml() {
  const sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  if (!sheet) {
    return 'Error: Sheet not found.';
  }

  // Get all data from the sheet (display values to evaluate formulas)
  const data = sheet.getDataRange().getDisplayValues();
  const images = sheet.getImages();

  // Create a map of images by their row and column
  let imageMap = {};
  images.forEach(image => {
    const row = image.getAnchorCell().getRow();
    const column = image.getAnchorCell().getColumn();
    const imageData = image.getAs("image/jpeg").getBytes();
    console.log("Image data:", imageData); // Add this logging statement
    imageMap[`${row}-${column}`] = imageData;
  });

  // Create HTML form and table
  var htmlOutput = "<html><head><style>";
  htmlOutput += "body { font-family: 'Helvetica', 'Arial', sans-serif; }"; // Add CSS for font
  htmlOutput += "table { border-collapse: collapse; width: 100%; }"; // Add CSS for table
  htmlOutput += "th, td { border: 1px solid #ddd; padding: 8px; }"; // Add CSS for table cells
  htmlOutput += "th { background-color: #f2f2f2; }"; // Add CSS for table header
  htmlOutput += "</style></head><body>";
  
  htmlOutput += "<form id='searchForm' onsubmit='return submitForm()'>";
  
  // Add input fields for D2 and D3 at the top
  htmlOutput += "<div>";
  htmlOutput += "<label for='userInputD2'>Enter value for D2: </label>";
  htmlOutput += "<input type='text' id='userInputD2' placeholder='Enter value for D2'><br>";
  htmlOutput += "<label for='userInputD3'>Enter value for D3: </label>";
  htmlOutput += "<input type='text' id='userInputD3' placeholder='Enter value for D3'><br>";
  htmlOutput += "<button type='submit'>Submit</button>";
  htmlOutput += "</div><br>";

  htmlOutput += "<table>";

  // Create table headers from the first row
  htmlOutput += "<tr>";
  for (var i = 0; i < data[0].length; i++) {
    htmlOutput += "<th>" + data[0][i] + "</th>";
  }
  htmlOutput += "</tr>";

  // Create table rows with data, excluding rows 2 and 3
  for (var i = 1; i < data.length; i++) {
    if (i === 1 || i === 2) continue; // Skip rows 2 and 3
    htmlOutput += "<tr>";
    for (var j = 0; j < data[i].length; j++) {
      var value = data[i][j];
      // Check if there's an image in the cell
      var imageKey = `${i + 1}-${j + 1}`;
      if (imageMap[imageKey]) {
        var base64Image = Utilities.base64Encode(imageMap[imageKey]);
        value = "<img src='data:image/jpeg;base64," + base64Image + "' alt='Image' />";
      } else if (value.startsWith('=') && value.indexOf('IMAGE("') !== -1) {
        // Extract the image URL from the formula
        var imageUrl = extractImageUrlFromFormula(value);
        value = "<img src='" + imageUrl + "' alt='Image' />";
      }

      htmlOutput += "<td>" + (value ? value : "") + "</td>"; // Display blank cells as empty
    }
    htmlOutput += "</tr>";
  }
  htmlOutput += "</table>";

  htmlOutput += "</form>";

  // Include client-side script for form submission and data refresh
  htmlOutput += "<script>";
  htmlOutput += "function submitForm() {";
  htmlOutput += "var userInputD2 = document.getElementById('userInputD2').value;";
  htmlOutput += "var userInputD3 = document.getElementById('userInputD3').value;";
  htmlOutput += "google.script.run.withSuccessHandler(refreshPage).updateCells([userInputD2, userInputD3]);";
  htmlOutput += "return false;";
  htmlOutput += "}";
  htmlOutput += "function refreshPage() {";
  htmlOutput += "google.script.run.withSuccessHandler(updateHtml).getHtml();";
  htmlOutput += "}";
  htmlOutput += "function updateHtml(html) {";
  htmlOutput += "document.body.innerHTML = html;";
  htmlOutput += "}";
  htmlOutput += "</script>";

  htmlOutput += "</body></html>";

  return htmlOutput;
}

function updateCells(values) {
  try {
    const sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
    sheet.getRange("D2").setValue(values[0]);
    sheet.getRange("D3").setValue(values[1]);
    return "Cells D2 and D3 updated successfully.";
  } catch (error) {
    console.error("Error updating cells:", error);
    return "Error updating cells.";
  }
}
function extractImageUrlFromFormula(formula) {
  // Regex to match URLs with or without quotes
  const imageUrlRegex = /IMAGE("([^"]+)"|s+([^ ]+))/;
  const match = formula.match(imageUrlRegex);

  // Return the captured URL group if found, otherwise return an empty string
  return match ? match[1] || match[2] : ""; // Check both capture groups
}

Thiết kế website giá rẻ

Danh mục

Google Apps Script not displaying IMAGES in web app