api-example.gs

/**
 * @author Volodymyr Melnychuk <540991@i.ua>
 * https://github.com/MelnixDev
 */

/**
 * Copy the ID from your spreadsheet URL:
 * https://docs.google.com/spreadsheets/d/{SPREADSHEET_ID}/edit
 */
const SPREADSHEET_ID = '15q8896q5FambSL0j9NVwpzRagoN9hKPWrBwO4ph4V30';
const SHEET_NAME = 'API-example';
const DATA_COLUMN_COUNT = 6;

/**
 * Base method for getting info from Google SpreadSheet.
 *
 * @param {string} [gsId=SPREADSHEET_ID]
 *   Google Spreadsheet ID.
 * @return {{ss: *, apiExample: *}}
 */
function prepareGsData(gsId = SPREADSHEET_ID) {
  // Get all data from Google Spreadsheet. The default value also makes direct
  // execution from the Apps Script editor safe.
  const ss = SpreadsheetApp.openById(gsId);
  const apiExample = ss.getSheetByName(SHEET_NAME);

  if (!apiExample) {
    throw new Error(
      `Sheet "${SHEET_NAME}" was not found. Check the SHEET_NAME constant.`,
    );
  }

  return {
    apiExample,
    ss,
  };
}

/**
 * Base method for running Google app script.
 *
 * @return string
 *   Return Json with trade-in devices data.
 */
function doGet() {
  // Public Google spreadsheet ID.
  const gsId = SPREADSHEET_ID;

  // Prepare Google Sheets data.
  const gsData = prepareGsData(gsId);

  // Load data in the object.
  const apiExampleData = getApiExampleData(gsData.apiExample);

  // Generate the JSON response.
  return convertToJson(apiExampleData);
}

/**
 * Run this function from the Apps Script editor to verify the setup.
 */
function testApi() {
  console.log(doGet().getContent());
}

/**
 * Convert object to Json string.
 *
 * @return string
 *    Return converted json.
 */
function convertToJson(data) {
  const jsonEncode = JSON.stringify(data);

  return ContentService
    .createTextOutput(jsonEncode)
    .setMimeType(ContentService.MimeType.JSON);
}

/**
 * Prepare products data.
 *
 * @return
 *   Object with product data.
 */
function getApiExampleData(data) {
  const lastRow = data.getLastRow();
  const lastColumn = data.getLastColumn();

  // A sheet with headers only is a valid empty API response.
  if (lastRow < 2) {
    return {};
  }

  if (lastColumn < DATA_COLUMN_COUNT) {
    throw new Error(
      `Sheet "${SHEET_NAME}" must contain at least ${DATA_COLUMN_COUNT} columns.`,
    );
  }

  // Read data without the header row. Restrict the range to the six mapped
  // columns so unrelated cells do not alter the response.
  const apiExampleData = data
    .getRange(2, 1, lastRow - 1, DATA_COLUMN_COUNT)
    .getValues();

  /**
   * Describe mapping:
   * 0  ID
   * 1  Product Name
   * 2  Size
   * 3  Unit
   * 4  Price Unit
   * 5  Price
   */
  const products = {};

  // Mapping values.
  for (const row of apiExampleData) {
    const productId = row[0];

    // Ignore empty rows and keep the first row for each product ID.
    if (productId === '' || productId === null || products[productId]) {
      continue;
    }

    products[productId] = {
      id: productId,
      product_name: row[1],
      size: row[2],
      unit: row[3],
      priceUnit: row[4],
      price: row[5],
    };
  }

  return products;
}