GoogleSync - Auto Sync Function in version 4

Code.gs

 /**
  * Code.gs, the main script for GoogleSync
  * Please do not change this code unless I ask you :)
  * File Version 4.1
  * Date Modified: August 23, 2021
  */
  
// Add a custom menu to the active document, including a separator and a sub-menu.
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('GoogleSync V4.0')
      .addItem('Connect Sheet', 'WCGS_CONNECT_SHEET')
      .addSeparator()
      .addItem('Sync Data', 'WCGS_SYNC_SHEET')
      .addSeparator()
      .addItem('Fetch Categoris', 'WCGS_FETCH_CATEGORIES')
      .addSeparator()
      .addItem('Fetch Products', 'WCGS_CHUNKER')
      .addSeparator()
      .addItem('Reset', 'WCGS_UNLINK_PRODUCTS')
      // .addSubMenu(SpreadsheetApp.getUi().createMenu('Advance')
      //   .addItem('LINK', 'WCGS_LINK_DATA')
      //   .addSeparator()
      //   .addItem('UNLINK', 'WCGS_UNLINK_PRODUCTS')
      // )
      .addToUi();
}

function WCGS_CONNECT_SHEET(){

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sheet_name = sheet.getName();
  const sheet_header = WCGS.get_sheet_header_range(sheet).getValues();

  var data = {
    'method' : 'post',
    'payload' : {'sheet_name': sheet_name,
                'header_data': JSON.stringify(sheet_header),
                'sync_col': WCGS.get_sheet_sync_col(sheet),
                'stock_col':WCGS.get_sheet_stock_col(sheet),
                'debug_mode': WCGS.WCGS_DEBUG,
                'authcode': WCGS.get_setting('auth_code'),
                }
  };

  const endpoint = 'connect-store';
  // Logger.log(data);
  var resp = WCGS_DO_REST(data, endpoint);
  var data = JSON.parse(resp.getContentText());
  
  Logger.log(data);
  if( ! data.success ) {
    WCGS.show_alert(data.data);
  }else{
    WCGS.show_alert(data.data);
  }
}

function WCGS_SYNC_PRODCUT_SHEET_AUTO() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('products');
  const chunk_size = WCGS.get_chunk_size();
  const sheet_data = WCGS.get_sheet_synable_data(sheet);
  const sheet_header = WCGS.get_sheet_header_range(sheet).getValues();
  

  var chunked = CHUNK_DATA(sheet_data, chunk_size);
  
  WCGS_PROCESS_SYNC_CHUNKS(chunked, sheet, sheet_header);  
}

function WCGS_SYNC_SHEET(sheet=null) {

  var sheet = sheet || SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const chunk_size = WCGS.get_chunk_size();
  const sheet_data = WCGS.get_sheet_synable_data(sheet);
  const sheet_header = WCGS.get_sheet_header_range(sheet).getValues();
  

  var chunked = CHUNK_DATA(sheet_data, chunk_size);
  
  WCGS_PROCESS_SYNC_CHUNKS(chunked, sheet, sheet_header);  
}

// Process chunks
function WCGS_PROCESS_SYNC_CHUNKS(chunks, sheet, sheet_header) {

  var chunk_count = 0;
  var total_chunks = chunks.length;
  for (var c = 0; c < total_chunks; c++) {

    // Logger.log(`chunking of ${chunks[c]}`);
    WCGS_SYNC_DATA_IN_CHUNKS(chunks[c] ,sheet, sheet_header, function(resp_msg) {

        chunk_count++;
        var ui = SpreadsheetApp.getActiveSpreadsheet();
        var run = (chunk_count / total_chunks) * 100;

        ui.toast(resp_msg,`Progress: ${run.toFixed(2)}%`);

        if (chunk_count == total_chunks) {
          WCGS.show_alert('SYNC OPERATION COMPLETED SUCCESSFULLY !!');
        }
    });
  }

}

function WCGS_FETCH_PRODUCTS(args, callback){

  const sheet_name = 'products';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  const sheet_header = WCGS.get_sheet_header_range(sheet).getValues();

  var data = {
    'method' : 'post',
    'payload' : {'sheet_name': sheet_name,
                'header_data': JSON.stringify(sheet_header),
                'sync_col': WCGS.get_sheet_sync_col(sheet),
                'chunk_size': WCGS.get_chunk_size(),
                'request_args': JSON.stringify(args),
                'debug_mode': WCGS.WCGS_DEBUG
                },
  };

  const endpoint = 'fetch-products';
  var resp = WCGS_DO_REST(data, endpoint);
  Logger.log(resp);
  var data = JSON.parse(resp.getContentText());
    
  if( ! data.success ) {
    WCGS.show_alert(data.data);
  }else{
    const {update: productsUpdate, create:productsCreate} = data.data;    
    
    // Updating existing rows after Convertion from Object2Array
    if( productsUpdate ){
      UPDATE_EXISTING_ROW(sheet_name, productsUpdate);
    }

    if( productsCreate ){
      APPEND_NEW_ROWS(sheet_name, productsCreate, function(){
        WCGS_LINK_DATA(sheet);
      });
    }

    callback(productsUpdate, productsCreate);

  }

}

function WCGS_DO_REST(data, endpoint) {

  const site_url = WCGS.get_setting('site_url');    
  var resp = UrlFetchApp.fetch(`${site_url}/wp-json/wcgs/v1/${endpoint}`, data);
  return resp;  
}

function UPDATE_EXISTING_ROW(sheet_name, data) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  const objectArray = Object.entries(data);
  const last_cell = WCGS.get_sheet_sync_col(sheet);
  objectArray.forEach(([index, value]) => {
    const range = `A${index}:${last_cell}${index}`; 
    // Logger.log(range);
    sheet.getRange(range).setValues([value]);
  });
}

function APPEND_NEW_ROWS(sheet_name, data, callback) {

  // Logger.log(values);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  var lastRow = sheet.getLastRow();
  var startRow = lastRow + 1;
  var totalRows = data.length;
  var last_cell = data[0].length;
  sheet.getRange(startRow,1, totalRows, last_cell).setValues(data);

  last_cell = WCGS.get_sheet_sync_col(sheet);
  var last_row = startRow + totalRows;
  // Logger.log(`${last_cell}${startRow}:${last_cell}${last_row}`);
  //Clear the sync col
  sheet.getRange(`${last_cell}${startRow}:${last_cell}${last_row}`).clearContent();
  callback();

}

function WCGS_UNLINK_PRODUCTS() {

  var data = {
    'method' : 'post',
    'payload' : {'sheet_name': SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(),
                'debug_mode': WCGS.WCGS_DEBUG
                },
  };

  const endpoint = 'unlink-rows';
  var resp = WCGS_DO_REST(data, endpoint);
  var data = JSON.parse(resp.getContentText());
  Logger.log(data);
}

function WCGS_LINK_DATA(sheet1){  

    const sheet = sheet1 || SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const sheet_name = sheet.getName();
    const sheet_data = WCGS.get_sheet_data(sheet);
    const id_cell = WCGS.get_col_by_name(sheet, 'id')-1;
    const sync_cell = WCGS.get_col_by_name(sheet, 'sync')-1;

    var row_no = 2;
    var data_with_id_sync = sheet_data.map(item => {
        var item2 = {'id': item[id_cell], 'sync': item[sync_cell], 'rowno':row_no++};
        return item2;
    });

    const syncable_rows = data_with_id_sync.filter(function(row){
      return row.sync !== 'OK';
    });   

    var data = {
      'method' : 'post',
      'payload' : {'product_rows': JSON.stringify(syncable_rows),'sheet_name':sheet_name, 'sync_col': WCGS.get_sheet_sync_col(sheet)}
    };

    const endpoint = 'link-data';
    var resp = WCGS_DO_REST(data, endpoint);
    // var data = JSON.parse(resp.getContentText());
    // Logger.log(data);
    // if(data.success){
    //   var ui = SpreadsheetApp.getUi();
    //     var response = ui.alert('Good Job, data is Linked with store!', ui.ButtonSet.OK);
    // }
  }

function WCGS_DELETE_ROW(sheet, row){
  sheet.deleteRow(row);
}


function AUTO_SYNC_ROW(row_data) {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sheet_name = sheet.getName();
  const sheet_header = WCGS.get_sheet_header_range(sheet).getValues();  
  
  const endpoint = 'sync-sheet-data';

  row_data = row_data.map(function(row){
            row.push('AUTO');
            var activeRange = SpreadsheetApp.getActiveRange();
            const rowNo = parseInt( activeRange.getRow() );
            const row_id = sheet_name == 'products' ? [{'key':'wcgs_row_id','value':rowNo}] : rowNo;
            row.push(row_id);
            return row;
  });

  var data = {
    'method' : 'post',
    'payload' : {'sheet_name': sheet_name,
                'header_data': JSON.stringify(sheet_header),
                'sync_col': WCGS.get_sheet_sync_col(sheet),
                'sheet_data': JSON.stringify(row_data),
                'debug_mode': WCGS.WCGS_DEBUG,
                'request_type': endpoint
                }
  };  
  // Logger.log(data);
  var resp = WCGS_DO_REST(data, endpoint);
  var data = JSON.parse(resp.getContentText());
  
  if( ! data.success ) {
    WCGS.show_alert(data.data);
  }else{
    const {data: ranges} = data;
    var alert_msg = '';
    var success_rows = 0;

    // Logger.log(ranges); return;

    ranges.forEach(function(item){     
     if( item.row == 'ERROR' ) {
       alert_msg += `<p style="color:red">FAILED: ${item.message} (Resource ID: ${item.id})</p><hr>`;
       return;
     }     
    
      // Updating id col
      sheet.getRange(`${WCGS.get_sheet_id_col(sheet)}${item.row}`).setValues([[item.id]]);
      // Updating sync col
      // sheet.getRange(`${WCGS.get_sheet_sync_col(sheet)}${item.row}`).setValues([['OK']]);
      if(item.image){
        // Updating image col with id
        sheet.getRange(`${WCGS.get_sheet_image_col(sheet)}${item.row}`).setValues([[item.image]]);
      }

      if(item.images){
        // Updating image col with id
        sheet.getRange(`${WCGS.get_sheet_images_col(sheet)}${item.row}`).setValues([[item.images]]);
      }     
  
      success_rows++;
    });

    alert_msg += `SUCCESS: Total ${success_rows} record(s) udpated successfully`;
    WCGS.show_dialog(alert_msg);    
  }
  return 'OK';

}