A jscript use excel object model api, activex xmlhttp and filesystem object.

jscript-with-excel-ajax-fso.js

/* License under GPLv3 by gholk
 *
 * share for reference if anyone need to deal with excel object model in jscript/vbscript and other stuff.
 */

// import JSON

// # lookup code
var debugFlag = ''

function alert(string) {
	WScript.Echo(string)
}
function debug(string, flag) {
	if (flag && debugFlag.match(flag)) alert(string)
	else if (flag == null && debugFlag) alert(string)
}
function sleep(second) {
	WScript.Sleep(second * 1000)
}
function getArgv(i) {
	var wa = WScript.Arguments
	if (i < 0) return wa.Count()
	else if (i == 0) return WScript.ScriptFullName
	else return wa(i-1)
}

var lookupEr = {
	WScript: WScript,
	init: function () {
		this.excel = this.WScript.CreateObject('Excel.Application')
		this.excel.Visible = true
		// this.excel = new ActiveXObject('Excel.Sheet')
		this.table = {}
	},
	loadTsvString: function (tsv) {
		var line = tsv.split(/;/)
		for (var i=0; i<line.length; i++) {
			var pair = line[i].split(/\t/)
			if (pair.length == 2) this.table[pair[0]] = pair[1]
		}
	},
	getCell: function (i, j, sheet) {
		var sheetObject
		if (sheet) sheetObject = this.book.Sheets(sheet)
		else sheetObject = this.book.ActiveSheet
		return sheetObject.Cells(i+1, j+1).Value
	},
	setCell: function (i, j, value, sheet) {
		var sheetObject
		if (sheet) sheetObject = this.book.Sheets(sheet)
		else sheetObject = this.book.ActiveSheet
		sheetObject.Cells(i+1, j+1).Value = value
	},
	open: function (file) {
		this.book = this.excel.Workbooks.Open(file)
	},
	saveAs: function (file) {
		this.book.saveAs(file)
	},
	save: function () {
		this.book.Save()
	},
	close: function () {
		this.excel.Quit()
	},
	getRow: function (i, count) {
		var a = []
		var j = 0
		var v
		while (true) {
			debug('get cell ' + [i,j], 'get-cell')
			v = this.getCell(i, j)
			if (count == null && !v) break
			else if (j >= count) break
			a.push(v)
			j += 1
		}
		return a
	},
	pickFile: function () {
		// not work
		var shell = this.WScript.CreateObject('WScript.Shell')
		var exec = shell.Exec("mshta.exe \"about:<input type=file id=FILE><script>FILE.click();new ActiveXObject('Scripting.FileSystemObject').GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);</script>\"")
		return exec.StdOut.ReadLine()
	},
	openAuto: function () {
		var file
		if (getArgv(-1) == 1) file = getArgv(1)
		else file = this.pickFile()
		return file
	}
}

var tsv = "\
the tsv data censored\
".replace(/,/g, '\t')

lookupEr.init()
lookupEr.loadTsvString(tsv)

// # main

var clAjax = {
	init: function () {
		this.fso = WScript.CreateObject('Scripting.FileSystemObject')
		this.cacheJson = true
		this.fetch5QueueArray = []
		this.fetch5QueueSize = 25
	},
	writeFile: function (file, string) {
		var overwrite = true
		var file = this.fso.CreateTextFile(file, overwrite)
		file.Write(string)
		file.Close()
	},
	queryStringEncode: function (object) {
		var list = []
		for (var key in object) {
			list.push(
				encodeURIComponent(key) + '=' +
				encodeURIComponent(String(object[key]))
			)
		}
		return list.join('&')
	},				
	fetch: function (url, option) {
		var xhr = WScript.CreateObject('MSXML2.XMLHTTP.6.0')
		var method = 'GET'
		if (option.method) method = option.method
		if (method == 'GET' && option.data) {
			if (typeof option.data == 'string') {
				url += '?' + option.data
			}
			else url += '?' + this.queryStringEncode(option.data)
			option.data = null
		}
		debug(method + ' ' + url, 'xhr-open')
		xhr.open(method, url, false)
		var json = false
		var header = option.header
		if (header) {
			for (var key in header) {
				xhr.setRequestHeader(key, header[key])
				debug(key + ':' + header[key], 'xhr-set-header')
			}
			if (header['Content-Type'] && header['Content-Type'].match(/^applications.json/)) {
				json = true
			}
		}
		var data
		if (option.data) {
			if (typeof option.data == 'string') data = option.data
			else if (json) {
				data = JSON.stringify(option.data)
			}
			else data = this.queryStringEncode(option.data)
		}
		if (data) xhr.send(data), debug(data, 'xhr-send')
		else xhr.send()
		debug(xhr.status, 'xhr-response-head')
		debug(xhr.getAllResponseHeaders(), 'xhr-response-head')
		return xhr.responseText
	},
	fetch1: function (url, data) {
		var result = this.fetch(url, {
			method: 'POST',
			header: {
				'Content-Type': 'applications/json; charset=utf-8',
				'Accept': 'application/json',
				'Authorization': 'Basic the+secret+base64+encrypt=='
			},
			data: data
		})
		return JSON.parse(result)
	},
	fetch2: function (path, data) {
		var base = 'https://api.server.io'
		return this.fetch1(base + path, data)
	},
	fetch3: function (name, method, version, data) {
		var url = 'https://some.api.io'
		url += name + '.ashx'
		url += '?' + this.queryStringEncode({mode: method, version: version})
		debug(url)
		var result = this.fetch(url, {
			method: 'POST',
			header: {'Content-Type': 'applications/json; charset=utf-8'},
			data: data
		})
		return JSON.parse(result)
	},
	reasonCache: {},
	reasonDecode: function () {
	},
	fetch6: function () {
		var result = this.fetchPay(

		)
		if (this.cacheJson) {
			this.writeFile( /* censored */ result)
		}
		try {
			return result /* censored */ 
		}
		catch (error) {
			alert(error)
		}
		return null
	},
	fetch5: function (single, callback) {
		var queue = this.fetch5QueueArray
		queue.push({single: single, callback: callback})
		if (queue.length != this.fetch5QueueSize) return

		var data = []
		for (var i=0; i<queue.length; i++) {
			data.push(queue[i].single)
		}
		var result = this.fetchPay('api-endpoint', data)
		// debug(JSON.stringify(result, null, ' '))
		var response = result /* censored */ 
		for (var i=0; i<response.length; i++) {
			queue[i].callback(response[i])
		}
		this.fetch5QueueArray = []
		return response
	},
	padLeft: function (s, length, c) {
		while (s.length < length) {
			s = c + s
		}
		return s
	}
}
function groupBy(f, a) {
	var group = {};
	for (var i=0; i<a.length; i++) {
		var key = f(a[i]);
		if (key in group) group[key].push(a[i]);
		else group[key] = [a[i]]
	}
	return group
}
Array.prototype.any = function (f) {
	for (var i=0; i<this.length; i++) {
		if (!f(this[i])) return false
	}
	return true
}
debugFlag = 'true'
lookupEr.fetchSelectRow = function () {
	var range = this.excel.Selection
	var rowStart = range.Row - 1
	var height = range.Rows.Count
	var that = this
	function fetchRow(value, row, sheet) {
		// censored
		clAjax.fetch5(
			{ /* censored */ },
			function callback(result) {
				if (!result) {
					alert('empty-response: '+ [ /* censored */ ].join(' '))
					return
				}
				var reason = clAjax.reasonDecode(unit  /* censored */ )
				that.setCell(row, 13, reason, sheet)
			}
		)
	}
	
	var oldSize = clAjax  /* censored */ 
	clAjax.fetch5QueueSize = height
	for (var i=0; i<height; i++) {
		var row = this.getRow(rowStart + i)
		var unit = this.find([row[2], row[3], row[5]])
		var sec = row[4]
		var no = row[6]
		fetchRow([ /* censored */ ], rowStart+i, null)
	}
	clAjax.fetch5QueueSize = oldSize
}
lookupEr.run = function () {
	var path = clAjax.fso.GetAbsolutePathName('all.xlsx')
	this.openSheet(path)
	// lookupEr.saveAs('all-result.xlsx')
	var that = this
	this.forEach(function (value, row, sheet) {
		alert([ /* censored */ ].join(' '))
		// censored
		clAjax /* censored */ (
			{ /* censored */ },
			function callback(result) {
				if (!result /* censored */ ) {
					alert('empty-response: '+ [ /* censored */ ].join(' '))
					return
				}
				var reason = clAjax.reasonDecode( /* censored */ )
				that.setCell(row, 13, reason, sheet)
			}
		)
	})
}
lookupEr.forEach = function (callback) {
	for (var i=2; i<=this.excel.Worksheets.count; i++) {
		this.excel.Worksheets(i).Activate()
		for (var j=1; true; j++) {
			var row = this.getRow(j, 7)
			if (row.length == 0 || !row.join('')) break
			if (row.length < 6) continue
			// if (this.getCell(j, 13)) continue
			var array = [row[2], row[3], row[5]]
			callback([ /* censored */ ], j, i)
		}
	}
}
Object.create = function (parent) {
	var Klass = function () {}
	Klass.prototype = parent
	return new Klass()
}
var agAjax = Object.create(clAjax)
agAjax.init = function (token) {
	this.fso = WScript.CreateObject('Scripting.FileSystemObject')
	this.baseUrl = 'http://secret.api.io'
	this.token = token
}
agAjax.fetchApi = function (path, option) {
	var data = option.data
	if (!('token' in data)) data.token = this.token
	if (!option.header) option.header = {}
	option.header.Cookie = 'bisquit'
	var url = this.baseUrl + path
	var json = this.fetch(url, option)
	debug('response: ' + JSON.stringify(json), 'xhr-response')
	return JSON.parse(json)
}
agAjax.queryById = function (id, field) {
	if (!field) field = '*'
	if (typeof id == 'string') id = [id]
	var idTuple = "('" + id.join("','") + "')"
	return this.fetchApi('query', {
		data: {
			f: 'json',
			where: 'ID in ' + idTuple,
			returnGeometry: false,
			spatialRel: 'esriSpatialRelIntersects',
			outFields: field
		}
	})
}
Array.prototype.map = function (f) {
	var copy = this.slice()
	for (var i=0; i<this.length; i++) copy[i] = f(this[i],i,this)
	return copy
}
Array.prototype.forEach = Array.prototype.map
agAjax.queryLandEye = function (id) {
	var result = this.queryById(id /* censored */ )
	var feature = result.features
	var idOrder = []
	featuer.forEach(function (x) {
		var index = id.indexOf( /* censored */ )
		idOrder[index] = x
	})
	return idOrder.map(function (x) { return x.attributes['censored'] })
}
lookupEr.run2 = function (token, file) {
	agAjax.init(token)
	
	this.openSheet(agAjax.fso.GetAbsolutePathName(file))
	var batch = {queue: [], size: 25}
	var that = this
	debugFlag = 'xhr-response-head xhr-set-header xhr-open xhr-send'
	this.forEach(function (value, i, sheet) {
		if (sheet == 1) return
		if (that.getCell(i, 15, sheet)) return
		debug(value, 'id')
		// censored
		debug('queue ' + id)
		batch.queue.push({id: id, callback: function (x) {
			that.setCell(i, 15, x, sheet)
		}})

		if (batch.queue.length >= batch.size) {
			debug('query send')
			var idList = batch.queue.map(function (x) { return x.id })
			idList.forEach(function (x, i) {
				batch.queue[i].callback(x)
			})
			batch.queue = []
			sleep(3)
		}
	})
}


// repl()
// agAjax.init()
lookupEr.run2(WScript.Arguments(0), WScript.Arguments(1))


function repl() {
	while (true) {
		var line = WScript.StdIn.ReadLine()
		if (line == '.exit') break
		var result
		try {
			result = eval(line)
		}
		catch (error) {
			result = error.message
		}
		alert(String(result))
	}
}

function checkXhr(e) {
	alert(String(xhr.readyState))
	if (xhr.readyState == 4) alert(xhr.responseText)
}

function alert(s) {
	WScript.Echo(s)
}
var fso = new ActiveXObject('Scripting.FileSystemObject')

function vb(s) {
	var vbe = /* WScript.CreateObject */ new ActiveXObject('ScriptControl')
	vbe.Language = 'VBScript'
	return vbe.eval(s)
}

// var x = vb('InputBox("Enter a String")')

function browserCode() {
  var agAjax = {}

  function sleep(s) {
    return new Promise(wake => setTimeout(wake, s * 1000))
  }
  agAjax.init = function(token) {
    this.batch = {
      queue: [],
      size: 25
    }
    this.result = []
    this.baseUrl = 'http://secret.safe.api'
    this.token = token
  }
  agAjax.fetchApi = async function(path, option) {
    var data = option.data
    if (!('token' in data)) data.token = this.token
    var url = this.baseUrl + path + '?' + this.queryStringEncode(data)
    var response = await this.fetch(url)
    var json = response.json()
    return json
  }
  agAjax.queryById = function(id, field) {
    if (!field) field = '*'
    if (typeof id == 'string') id = [id]
    var idTuple = "('" + id.join("','") + "')"
    return this.fetchApi('query', {
      data: {
        f: 'json',
        where: 'ID in ' + idTuple,
        returnGeometry: false,
        spatialRel: 'esriSpatialRelIntersects',
        outFields: field
      }
    })
  }
  agAjax.query2 = async function(id) {
    var result = await this.queryById(id, 'f1,f2'))
    var feature = result.features
    // console.log(result)
    var idOrder = []
    feature.forEach(function(x) {
      var index = id.indexOf(x /* censored */ )
      idOrder[index] = x
    })
    debugger
    return idOrder.map(function(x) {
      return x.attributes['f1']
    })
  }
  agAjax.queryStringEncode = function(obj) {
    const qs = new URLSearchParams(obj)
    return qs.toString()
  }
  agAjax.fetch = u => fetch(u)


  agAjax.queryQueue = function(id) {
    return new Promise(async ok => {
      var batch = this.batch
      batch.queue.push({
        id,
        callback: ok
      })
      if (batch.queue.length < batch.size) return

      const queue = batch.queue
      batch.queue = []
      const idList = queue.map(o => o.id)
      if (this.wait) this.wait = this.wait.then(x => sleep(3))
      else this.wait = sleep(3)
      await this.wait
      const result = await this.query2(idList)
      queue.forEach((o, i) => o.callback(result[i]))
    })
  }

  agAjax.queryBatch = async function(list) {
    return Promise.all(list.map(async id => this.result.push(await this.queryQueue(id))))
  }
  return agAjax
}