Generating the WebApp URL for Autch Fetch for Bulk Product Sync Plugin

Code.gs

/**
 * AutoFetch, AutoSync
 * Bulk Product Sync for WooCommerce.
 * 
 * June 7, 2024
 * AutoSync Feature Fixed
 * 
 * Last October 16, 2023
 * AutoFetch: AutoSync issue fixed when parents are empty.
 * 
 * Last October 12, 2023
 * AutoFetch: cells with formulas will not be updated.
 * 
 * Last updated May 25, 2023
 * AutoSync issues fixed with custom meta
 * AutoSync issues fixed with variations
 * 
 * Last updated May 19, 2023
 * show_alert method replaced with Logger.log inside validate_sheet_header_keys method
 * 
 * Last updated May 6, 2023
 * AutoSync issue fixed.
 */
function doPost(e) {
    
    var data = JSON.parse(e.postData.contents);
    
    let {row_id, rows, product_id, sheet_props} = data;
    const {id_col, sync_col, store_url} = sheet_props;
    const {event_type, sheet_name} = e.parameter;
    
    let response = {'message':'Product updated'};

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);

    if( event_type === 'product_created' || event_type === 'product_restored' ) {
      row_id = sheet.getLastRow()+1;
      for (var key in rows) {
        var data = [rows[key]];
        update_sheet_with_data(sheet, row_id, data, id_col, sync_col);
        link_new_product_with_store(store_url, product_id, row_id);
        row_id++;
      }      
    } else if(event_type === 'product_updated' && row_id) {
      for (var key in rows) {
        row_id = key;
        var data = [rows[key]];
        update_sheet_with_data(sheet, row_id, data, id_col, sync_col);
      }
      
    } else if(event_type === 'product_deleted' && row_id) {
      var row_range = sheet.getRange(`${id_col}${row_id}`);
      // delete the row
      sheet.deleteRow(row_range.getRow());
      relink_all_products_from_sheet_to_store(store_url, sheet, id_col);
    }

    return;

          
    // Return the JSON response
    response = JSON.stringify(response);
    var output = ContentService.createTextOutput(response);
    output.setMimeType(ContentService.MimeType.JSON);
    return output;
  
}

// function update_sheet_with_data(sheet, row_id, data, id_col, sync_col){
//   sheet.getRange(`${id_col}${row_id}:${sync_col}${row_id}`).setValues(data);
// }

function getChunkSize() {
  return 10;
}

function update_sheet_with_data(sheet, row_id, data, id_col, sync_col) {
  // Fetch the existing formulas of the row
  var existingRowFormulas = sheet.getRange(`${id_col}${row_id}:${sync_col}${row_id}`).getFormulas()[0];

  data = data[0];

  // Iterate through the row values and update only non-null and non-empty values
  for (var i = 0; i < existingRowFormulas.length; i++) {
    // If the cell originally had a formula, retain the formula
    if (existingRowFormulas[i] !== null && existingRowFormulas[i] !== '') {
      // Do nothing, keep the formula
    } else {
      // Update the cell with the new value even if it's null or empty
      existingRowFormulas[i] = data[i];
    }
  }

  // Write the updated row back to the sheet
  sheet.getRange(`${id_col}${row_id}:${sync_col}${row_id}`).setValues([existingRowFormulas]);
}


function link_new_product_with_store(store_url, product_id, row_id){
  const url = `${store_url}/wp-json/wbps/v1/link-new-product`;
  const options = {method: 'POST', payload:{product_id, row_id}};

  var response = UrlFetchApp.fetch(url, options);
  // Logger.log(response);
}

function relink_all_products_from_sheet_to_store(store_url, sheet, id_col) {
  const lastRow = sheet.getLastRow();
  const range = `${id_col}2:${id_col}${lastRow}`;

  // get the range for the row to delete
  var values = sheet.getRange(range).getValues();
  var output = values.map(function(productId, index) {
    return { product_id: productId[0], row_id: index + 2 };
  });

  // return Logger.log(output);

  url = `${store_url}/wp-json/wbps/v1/relink-products`;
  const options = {method: 'POST', payload: {product_links: JSON.stringify(output)}};

  UrlFetchApp.fetch(url, options);

  // Logger.log(response);
  }
  /**
   * =========== AutoSync ==================
   */

function AutoSync() {
  
  const sheet_name = 'products';
  var all_rows = SpreadsheetApp.getActive().getSheetByName(sheet_name).getDataRange().getDisplayValues();

  var syncable_data = get_syncable_data(all_rows);
 
  if(syncable_data.length === 0){
    return Logger.log('No Data to Sync.')
  }

  
  syncable_data = separateDataForCreatingAndUpdating(syncable_data);
  Logger.log(syncable_data);
  // return;
  let variations = syncable_data.variations;
  delete syncable_data.variations;
 
  const {store_url, consumer_key, consumer_secret} = get_settings_from_sheet();
  const id_col = get_value('id_col');
  const sync_col = get_value('sync_col');

  var url = `${store_url}/wp-json/wc/v3/products/batch`;
  
  
  try {
    var options = {
      method: 'POST',
      contentType: 'application/json',
      headers: {
        'Authorization': 'Basic ' + Utilities.base64Encode(`${consumer_key}:${consumer_secret}`)
      },
      payload: JSON.stringify(syncable_data),
      muteHttpExceptions: false
    };
    
    
    var response = UrlFetchApp.fetch(url, options);
    var data = response.getContentText();
    response = JSON.parse(data);

    // Logger.log(response);

    const {update, create} = response;
    ss = SpreadsheetApp.getActive().getSheetByName('products');


    if (update) {
      var wbpsRowIds = update.map(function(result) {
        var meta = result.meta_data ? result.meta_data.find(function(item) {
          return item.key === 'wbps_row_id';
        }) : undefined;
        return meta ? meta.value : undefined;
      });   
      wbpsRowIds.forEach(function(row) {
        if (row) {
          ss.getRange(`${sync_col}${row}`).setValues([['OK']]);
        }
      });
    }

    if( create ) {
      
      // new products
      var new_products = create.map(function(result) {
        var meta = result.meta_data ? result.meta_data.find(function(item) {
          return item.key === 'wbps_row_id';
        }) : undefined;
        var row_id = meta ? meta.value : undefined;
        return {row_id: row_id, id: result.id};
      });


      new_products.map(product => {
          ss.getRange(`${sync_col}${product.row_id}`).setValues([['OK']]);
          ss.getRange(`${id_col}${product.row_id}`).setValues([[product.id]]);
      });

    }

    // Logger.log(variations);
    // variations products
    if( variations ) {

        // removing the keys, so we have only array of variations
        variations = variations.map(d => Object.values(d)[0]);
        

        variations.forEach(function(variation){
          // let row_id = Object.keys(variation)[0];
          // variation = Object.values(variation)[0];
          // Logger.log(variation);
          
          url = `${store_url}/wp-json/wc/v3/products/${variation.parent_id}/variations`;
          const is_new = !variation.id;
          const method = is_new ? 'POST' : 'PUT';
          url = is_new ? url : `${url}/${variation.id}`;
          delete variation.id; // delete id key
          var options = {
            method,
            contentType: 'application/json',
            headers: {
              'Authorization': 'Basic ' + Utilities.base64Encode(`${consumer_key}:${consumer_secret}`)
            },
            payload: JSON.stringify(variation),
            muteHttpExceptions: false
          };

          // Logger.log(variation);
          // Logger.log(url);

          var response = UrlFetchApp.fetch(url, options);
          var data = response.getContentText();
          response = JSON.parse(data);

          // Logger.log(response);

          var meta = response.meta_data ? response.meta_data.find(function(item) {
            return item.key === 'wbps_row_id';
          }) : undefined;
          if( is_new ){
             ss.getRange(`${id_col}${meta.value}`).setValues([[response.id]]);
          }
          ss.getRange(`${sync_col}${meta.value}`).setValues([['OK']]);
          // Logger.log(meta);
        })
        
        
    }

    // return Logger.log(new_products); 

    // Logger.log(new_products);
    // Perform other actions here

  } catch (e) {
    Logger.log('Error while connecting your site: ' + e.toString());
  }
}




  /**
 * Get Syncable Products/Categories etc
 */

function get_syncable_data(all_rows) {


  // verifying and also saving important col letters
  let rows = [...all_rows];
  const sheet_name = 'products';
  if( ! validate_sheet_header_keys(sheet_name, all_rows) ) {
      return {success:false,message:'The keys are not valid'};
  }

  // remove first (header) row:
  var header = rows.shift();

  // get correct keys for header
  let mapping = get_header_mapped_data(sheet_name);
  // Logger.log(rows.length);
  header = header.map(cell => mapping.find(m => m.label === cell)['key']);

  // check for custom meta
  const custom_meta = mapping.filter(data => data.source === 'custom' && header.includes(data.key)).map(data => data.key);
  // Logger.log(custom_meta);
   
  var syncable = rows.map( (row,index) => {
    // merging row data with header keys
    // const value = meta_data.includes(key) ? {key, value:row[index]} : row[index];
    let merged = header.reduce((obj, key, index) => ({ ...obj, [key]: row[index] }), {});
    
    var offset = 2;
    const rowno = index+offset;

    // product meta_data is being added here
    let meta_data = [{key:'wbps_row_id',value:rowno}];
    if(custom_meta){
      custom_meta.map(meta => {
        meta_data = [...meta_data, {key:meta,value:merged[meta]}];
      });
    }

    merged = {...merged, meta_data};
    // Logger.log(merged);
    return {[index+offset]:merged};
  })
  .filter(function(item){
    return Object.values(item).filter(i => i.sync !== 'OK').length > 0;
  });

  // Logger.log(syncable);
  syncable = prepare_data_for_wc(syncable);

  
  // return;

    
  return syncable;
}

// Preprating for batch update
function separateDataForCreatingAndUpdating(data) {
  var existingItems = [];
  var newItems = [];

  const variations = data.filter(obj => {
    const parent_id = Object.values(obj)[0].parent_id;
    return parent_id !== '' && parent_id !== "0";
  });

  const non_variations = data.filter(obj => {
    const parent_id = Object.values(obj)[0].parent_id;
    return parent_id === '' || parent_id === "0";
  });

  // Loop through the data array and separate the items into new and existing
  non_variations.forEach(function(obj) {
    var itemId = obj[Object.keys(obj)[0]].id;

    if (itemId) {
      existingItems.push(Object.values(obj)[0]);
    } else {
      newItems.push(Object.values(obj)[0]);
    }
  });

  const totalItems = newItems.length + existingItems.length + variations.length;
  const maxTotal = 100;

  const createRatio = newItems.length / totalItems;
  const updateRatio = existingItems.length / totalItems;
  const variationsRatio = variations.length / totalItems;

  const createChunkSize = Math.min(Math.ceil(createRatio * maxTotal), newItems.length);
  const updateChunkSize = Math.min(Math.ceil(updateRatio * maxTotal), existingItems.length);
  const variationsChunkSize = Math.min(Math.ceil(variationsRatio * maxTotal), variations.length);

  return {
    create: newItems.slice(0, createChunkSize),
    update: existingItems.slice(0, updateChunkSize),
    variations: variations.slice(0, variationsChunkSize)
  };
}





function prepare_data_for_wc(data) {
  return data.map(function(obj) {
    var keys = Object.keys(obj);
    var new_obj = {};
    
    keys.forEach(function(key) {
      var value = obj[key];
      
      if (typeof value === 'object') {
        // Process the object properties recursively
        value = prepare_data_for_wc([value])[0];
      } else if (key === 'categories' || key === 'images') {
        // Process the values of the "categories" and "images" keys
        value = value.split('|').map(function(item) {
          return { id: parseInt(item, 10) };
        });
      } else if (key === 'attributes' && value !== '') {
          value = JSON.parse(value);
      } else if (key === 'manage_stock' && value === ''){
          value = manage_stock = false;
      }else if (key === 'stock_quantity' && value === ''){
          value = stock_quantity = 0;
      }
      
      new_obj[key] = value;
    });
    
    return new_obj;
  });
}


function get_settings_from_sheet() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('settings');
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(1, 1, lastRow, 2);
  var values = range.getValues();
  var propertiesObject = {};
  
  for (var i = 0; i < values.length; i++) {
    var key = values[i][0];
    var value = values[i][1];
    
    if (key) {
      propertiesObject[key] = value;
    }
  }
  
  // Logger.log(propertiesObject);
  return propertiesObject;
}


function validate_sheet_header_keys(sheet_name, all_rows){

  const rows = [...all_rows];
  // remove header
  var header = rows.shift();
  
  const mapping = get_header_mapped_data(sheet_name);
  const valide_labels = mapping.map(m => m.label);
  const not_found = header.filter(col => !valide_labels.includes(col));
  // check if all columns are matched in mapping
  if(not_found.length > 0){
    Logger.log(`Following header(s) not valid \n${not_found.join(',')}`);
    return false;
  }
  // check if last column is 'sync'
  if( header[header.length-1] !== 'sync' ){
    Logger.log(`The 'sync' column should in the last header column`);
    return false;
  }
  /**
   * now saving id,images,sync etc cols letters
   */
  let config_data = {header:JSON.stringify(header)};

  // id col
  const {label:id_label} = mapping.find(cell => cell.key === 'id');
  let id_ind = header.indexOf(id_label)
  if( id_ind !==  -1){
    config_data.id_col = col2letter(id_ind+1);
  }

  // sync col
  const {label:sync_label} = mapping.find(cell => cell.key === 'sync');
  let sync_ind = header.indexOf(sync_label)
  if( sync_ind !==  -1){
    config_data.sync_col = col2letter(sync_ind+1);
  }
  
  // images col (products)
  if( sheet_name === 'products' ){
    const {label:images_label} = mapping.find(cell => cell.key === 'images');
    let images_ind = header.indexOf(images_label)
    if( images_ind !==  -1){
      config_data.images_col = col2letter(images_ind+1);
    }
  }

  // image col (variation, category)
  const {label:image_label} = mapping.find(cell => cell.key === 'image');
  let image_ind = header.indexOf(image_label)
  if( image_ind !==  -1){
    config_data.image_col = col2letter(image_ind+1);
  }

  // Logger.log(config_data);
  set_value(config_data);
  // save_properties_in_website();
  return config_data;
}

function get_header_mapped_data(sheet_name, stringify=false) {
  
  var header_mapping = null;
  switch(sheet_name){
    case 'products':
      let {product_mapping} = get_settings_from_sheet();
      if( product_mapping ){
        header_mapping = product_mapping;
      } else {
        header_mapping = product_header_mapping_default();
      }
      break;

      case 'categories':
        const category_mapping = '';
        if( category_mapping ){
          header_mapping = category_mapping;
        } else {
          header_mapping = category_header_mapping_default();
        }
        break;
  }  

  // to retain the keys order on client-side we need to pass objec as stringify
  return stringify ? header_mapping : JSON.parse(header_mapping);
}

// return column letter by index (starting: 1)
function col2letter(column){
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  // Logger.log(letter);
  return letter;
}

function product_header_mapping_default() {
 
  const mapping = [
  { key: "id", label: "id", type: "string", source:'core', return_value:'default' },
  { key: "name", label: "name", type: "string", source:'core', return_value:'default' },
  { key: "slug", label: "slug", type: "string", source:'core', return_value:'default' },
  { key: "permalink", label: "permalink", type: "string", source:'core', return_value:'default' },
  { key: "date_created", label: "date_created", type: "date", source:'core', return_value:'default' },
  {
    key: "date_created_gmt",
    label: "date_created_gmt",
    type: "date",
    source:'core', return_value:'default',
  },
  { key: "date_modified", label: "date_modified", type: "date", source:'core', return_value:'default' },
  { key: "status", label: "status", type: "string", source:'core', return_value:'default' },
  {
    key: "date_modified_gmt",
    label: "date_modified_gmt",
    type: "date",
    source:'core', return_value:'default',
  },
  { key: "type", label: "type", type: "string", source:'core', return_value:'default' },
  {
    key: "shipping_class_id",
    label: "shipping_class_id",
    type: "int",
    source:'core', return_value:'default',
  },
  {
    key: "catalog_visibility",
    label: "catalog_visibility",
    type: "string",
    source:'core', return_value:'default',
  },
  {
    key: "sold_individually",
    label: "sold_individually",
    type: "boolean",
    source:'core', return_value:'default',
  },
  {
    key: "short_description",
    label: "short_description",
    type: "string",
    source:'core', return_value:'default',
  },
  { key: "price", label: "price", type: "string", source:'core', return_value:'default' },
  {
    key: "download_expiry",
    label: "download_expiry",
    type: "int",
    source:'core', return_value:'default',
  },
  {
    key: "date_on_sale_from",
    label: "date_on_sale_from",
    type: "date",
    source:'core', return_value:'default',
  },
  {
    key: "regular_price",
    label: "regular_price",
    type: "string",
    source:'core', return_value:'default',
  },
  {
    key: "date_on_sale_to",
    label: "date_on_sale_to",
    type: "date",
    source:'core', return_value:'default',
  },
  { key: "sale_price", label: "sale_price", type: "string", source:'core', return_value:'default' },
  {
    key: "date_on_sale_from_gmt",
    label: "date_on_sale_from_gmt",
    type: "date",
    source:'core', return_value:'default',
  },
  { key: "sku", label: "sku", type: "string", source:'core', return_value:'default' },
  {
    key: "date_on_sale_to_gmt",
    label: "date_on_sale_to_gmt",
    type: "date",
    source:'core', return_value:'default',
  },
  { key: "price_html", label: "price_html", type: "string", source:'core', return_value:'default' },
  { key: "on_sale", label: "on_sale", type: "boolean", source:'core', return_value:'default' },
  { key: "purchasable", label: "purchasable", type: "boolean", source:'core', return_value:'default' },
  { key: "downloads", label: "downloads", type: "array", source:'core', return_value:'default' },
  {
    key: "download_limit",
    label: "download_limit",
    type: "int",
    source:'core', return_value:'default',
  },
  { key: "tags", label: "tags", type: "array", source:'core', return_value:'id' },
  { key: "virtual", label: "virtual", type: "boolean", source:'core', return_value:'default' },
  { key: "button_text", label: "button_text", type: "string", source:'core', return_value:'default' },
  { key: "tax_status", label: "tax_status", type: "string", source:'core', return_value:'default' },
  { key: "tax_class", label: "tax_class", type: "string", source:'core', return_value:'default' },
  { key: "weight", label: "weight", type: "string", source:'core', return_value:'default' },
  { key: "external_url", label: "external_url", type: "string", source:'core', return_value:'default' },
  { key: "stock_status", label: "stock_status", type: "string", source:'core', return_value:'default' },
  {
    key: "manage_stock",
    label: "manage_stock",
    type: "boolean",
    source:'core', return_value:'default',
  },
  {
    key: "grouped_products",
    label: "grouped_products",
    type: "array",
    source:'core', return_value:'default',
  },
  { key: "backorders", label: "backorders", type: "string", source:'core', return_value:'default' },
  {
    key: "backorders_allowed",
    label: "backorders_allowed",
    type: "boolean",
    source:'core', return_value:'default',
  },
  { key: "backordered", label: "backordered", type: "boolean", source:'core', return_value:'default' },
  {
    key: "downloadable",
    label: "downloadable",
    type: "boolean",
    source:'core', return_value:'default',
  },
  { key: "description", label: "description", type: "string", source:'core', return_value:'default' },
  { key: "categories", label: "categories", type: "array", source:'core', return_value:'id' },
  {
    key: "shipping_required",
    label: "shipping_required",
    type: "boolean",
    source:'core', return_value:'default',
  },
  {
    key: "shipping_taxable",
    label: "shipping_taxable",
    type: "boolean",
    source:'core', return_value:'default',
  },
  { key: "dimensions", label: "dimensions", type: "object", source:'core', return_value:'default' },
  {
    key: "shipping_class",
    label: "shipping_class",
    type: "string",
    source:'core', return_value:'default',
  },
  {
    key: "reviews_allowed",
    label: "reviews_allowed",
    type: "boolean",
    source:'core', return_value:'default',
  },
  {
    key: "average_rating",
    label: "average_rating",
    type: "string",
    source:'core', return_value:'default',
  },
  { key: "rating_count", label: "rating_count", type: "int", source:'core', return_value:'default' },
  { key: "related_ids", label: "related_ids", type: "array", source:'core', return_value:'default' },
  { key: "upsell_ids", label: "upsell_ids", type: "array", source:'core', return_value:'default' },
  { key: "parent_id", label: "parent_id", type: "int", source:'core', return_value:'default' },
  { key: "menu_order", label: "menu_order", type: "int", source:'core', return_value:'default' },
  {
    key: "purchase_note",
    label: "purchase_note",
    type: "string",
    source:'core', return_value:'default',
  },
  {
    key: "cross_sell_ids",
    label: "cross_sell_ids",
    type: "array",
    source:'core', return_value:'default',
  },
  { key: "attributes", label: "attributes", type: "array", source:'core', return_value:'default' },
  {
    key: "default_attributes",
    label: "default_attributes",
    type: "array",
    source:'core', return_value:'default',
  },
  { key: "variations", label: "variations", type: "array", source:'core', return_value:'default' },
  {
    key: "stock_quantity",
    label: "stock_quantity",
    type: "int",
    source:'core', return_value:'default',
  },
  { key: "images", label: "images", type: "array", source:'core', return_value:'id' },
  { key: "image", label: "image", type: "string", source:'core', return_value:'id' },  // for variation
  { key: "total_sales", label: "total_sales", type: "int", source:'core', return_value:'default' },
  { key: "featured", label: "featured", type: "boolean", source:'core', return_value:'default' },
  { key: "sync", label: "sync", type: "string", source:'plugin',return_value:'default' }
]; 

  return JSON.stringify(mapping);
}

function category_header_mapping_default() {

  const mapping = [
        { key: "id", label: "id", type: "int", source:'core', return_value:'default' },
        { key: "name", label: "name", type: "string", source:'core', return_value:'default' },
        { key: "slug", label: "slug", type: "string", source:'core', return_value:'default' },
        { key: "parent", label: "parent", type: "int", source:'core', return_value:'default' },
        { key: "count", label: "count", type: "int", source:'core', return_value:'default' },
        { key: "image", label: "image", type: "object", source:'core', return_value:'id' },
        { key: "description", label: "description", type: "string", source:'core', return_value:'default' },
        { key: "display", label: "display", type: "string", source:'core', return_value:'default' },
        { key: "menu_order", label: "menu_order", type: "int", source:'core', return_value:'default' },
        { key: "sync", label: "sync", type: "string", source:'plugin',return_value:'default' }
      ];

  return JSON.stringify(mapping);
}

function get_value(key){
  const properties = PropertiesService.getScriptProperties();
  return properties.getProperty(key);
}

function set_value(key_value){
  const scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperties(key_value);
  // // update sheet when new settings updated/added
  // const all_options = get_all_values();
  // if(all_options){
  //   save_properties_in_settings_sheet(all_options);
  // }
  
}

function testF(){
  const newData = [1848.0, 'Chicken Malai Boti Seekh 3', 'simple', 0.0, '', '', 200.0, 15.0, false, , 'OK'];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('products');
  const r = sheet.getRange(`A1125:K1125`).getValues()[0];
  // Iterate through the row values and update only non-null values
  for (var i = 0; i < r.length; i++) {
    if (newData[i] !== null && newData[i] !== '') {
      r[i] = newData[i];
    }
  }
  sheet.getRange(`A1125:K1125`).setValues([r]);

  Logger.log(newData);
}