oshliaer/.injector.en.md

## .injector.en.md

      
    Raw
  

              .injector.en.md
            
          
    Injector


This app connects to your spreadsheets by triggers.
You could try how it works https://script.google.com/macros/s/AKfycbyhCf2X3fVCD53rJfBjiz_Z6SDVTKTwnUdf2L2yNG7TpLgWFVpf/exec

Add an id of your Spreadsheet
Open it then find 'My menu' in main menu
Run


## gs_code.gs
function doGet(e) {
  return HtmlService.createTemplateFromFile('html_app').evaluate().setTitle('Injector');
}

function getTriggers(source){
  return ScriptApp.getProjectTriggers().map(function(tr){
    return {
      id: tr.getUniqueId(),
      source: tr.getTriggerSource(),
      sourceId: tr.getTriggerSourceId(),
      name: DriveApp.getFileById(tr.getTriggerSourceId()).getName(),
      handler: tr.getHandlerFunction(),
      source: source
    };
  });
}

function bindToSheet(id) {
  var functionName = 'createMenu';
  var res = switchTrigger(id, functionName, 'ON_OPEN');
  Utilities.sleep(1000);
  return getTriggers(res);
}

function switchTrigger(id, handlerFunction, eventType) {
  var isExist = false;
  var res = {
    deleted: false,
    added: false
  };
  var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.openById(id));
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === handlerFunction && triggers[i].getEventType() === ScriptApp.EventType[eventType]) {
      isExist = true;
      ScriptApp.deleteTrigger(triggers[i]);
      res.deleted = true;
    }
  }
  if (!isExist) {
    var newTrigger = ScriptApp.newTrigger(handlerFunction);
    res.added = true;
    newTrigger.forSpreadsheet(id).onOpen().create();
  }
  return res;
}

## gs_main.gs
function run(){
  SpreadsheetApp.getActiveSpreadsheet().toast(new Date(), 'run');
}

function run2(){
  SpreadsheetApp.getActiveSpreadsheet().toast(new Date(), 'run2');
}

function runCode(codeText){
  return eval(Utilities.formatString('function(){%s}', codeText))();
}

## gs_ui.gs
function createMenu(){
  SpreadsheetApp.getUi().createMenu('My menu')
  .addItem('run', 'run')
  .addItem('run2', 'run2')
  .addItem('About', 'showAbout')
  .addToUi();
}

function showAbout(){
  var userInterface = HtmlService.createTemplateFromFile('html_about').evaluate();
  SpreadsheetApp.getUi().showModalDialog(userInterface, 'About');
}

## html_about.html
<!DOCTYPE html>
<html>

<head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <style>
        #fixed-div {
            position: fixed;
            top: 1px;
            right: 1px;
        }

        .note {
            color: white;
            padding: 6px;
            border-radius: 1px;
        }

        .red {
            background-color: red;
        }

        .green {
            background-color: green;
        }
    </style>
</head>

<body>
    <div id="fixed-div"></div>
    <p>This app connects to your spreadsheets by triggers.</p>
    <p><a href="https://gist.github.com/oshliaer/a1f45e5ae8673dbe55b5074288651695" target="_blank">Gist of GitHub</a></p>
    <p>You could run some code from the field below:</p>
    <div>
        <div class="inline form-group">
            <input type="button" id="example1" value="Example #1" onclick="setExample(0)">
        </div>
        <div class="inline form-group">
            <input type="button" id="example2" value="Example #2" onclick="setExample(1)">
        </div>
        <div class="inline form-group">
            <input type="button" id="example3" value="Example #3" onclick="setExample(2)">
        </div>
    </div>
    <textarea style="width: 100%; height: 130px" id="codeText">
      var s = SpreadsheetApp.getActiveSpreadsheet();
      var name = s.getName();
      s.toast(name);
    </textarea>
    <div><input type="button" value="Run code" onclick="runCode()"></div>
    <script>
        var EXAMPLES = [];
        EXAMPLES.push(
            'var s = SpreadsheetApp.getActiveSpreadsheet();\nvar name = s.getName();\ns.toast(name);\nreturn name;'
        );
        EXAMPLES.push(
            'var s = SpreadsheetApp.getActiveSpreadsheet();\nvar value = s.getActiveCell().getDisplayValue();\ns.toast(value, "Active cell value is");\nreturn value;'
        );
        EXAMPLES.push(
            'var s = SpreadsheetApp.getActiveSpreadsheet();\nvar a1Nota = s.getActiveRange().getA1Notation();\ns.toast(a1Nota, "Active Range is");\nreturn a1Nota;'
        );
        var codeText = document.getElementById("codeText");
        var fixedDiv = document.getElementById("fixed-div");

        function runCode() {
            google.script.run
                .withSuccessHandler(function(e) {
                    notify(`Success! Result: ${e}`, 'green');
                })
                .withFailureHandler(function(e) {
                    notify(e.message, 'red');
                })
                .runCode(codeText.value);
        }

        function notify(text, _class) {
            console.log('notify', text);
            var note = appendNote(text, _class);
            window.setTimeout(function(note) {
                note.remove();
            }, 10000, note);
        }

        function appendNote(text, _class) {
            var currentDiv = document.createElement('div');
            currentDiv.innerHTML = text;
            currentDiv.className += ['note', _class].join(' ');
            fixedDiv.appendChild(currentDiv);
            return currentDiv;
        }

        Element.prototype.remove = function() {
            this.parentElement.removeChild(this);
        }
        NodeList.prototype.remove = HTMLCollection.prototype.remove = function() {
            for (var i = this.length - 1; i >= 0; i--) {
                if (this[i] && this[i].parentElement) {
                    this[i].parentElement.removeChild(this[i]);
                }
            }
        }

        function setExample(n) {
            n = (n >= 0 && n <= 3) ? n : 0;
            codeText.value = EXAMPLES[n];
        }

        setExample();
    </script>
</body>

</html>

## html_app.html
<!DOCTYPE html>
<html>

<head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>

<body>
    <div>
        <label for="spreadsheetId">Spreadsheet Id</label>
        <input type="text" id="spreadsheetId" style="width: 250px;"><input type="button" onclick="bindToSheet(spreadsheetId.value)" value="Bind/Unbind" />
    </div>
    <div id="listOfTriggers"></div>
    <script>
        var listOfTriggers = document.getElementById("listOfTriggers");
        var spreadsheetId = document.getElementById("spreadsheetId");

        function getTriggers() {
            google.script.run.withSuccessHandler(updateList).withUserObject(listOfTriggers).getTriggers();
        }

        function bindToSheet(id) {
            google.script.run.withSuccessHandler(updateList).withUserObject(listOfTriggers).bindToSheet(id);
        }

        function updateList(e, div) {
            while (div.firstChild)
                div.removeChild(div.firstChild);
            e.forEach(function(cV) {
                this.insertAdjacentHTML('beforeend', `<div>${cV.id} ${cV.handler} ${cV.sourceId} <a href="https://docs.google.com/spreadsheets/d/${cV.sourceId}/edit?usp=sharing" target="_blank">${cV.name}</a></div>`);
            }, div);
        }

        function emptyElement(el) {
            while (el.firstChild)
                el.removeChild(el.firstChild);
        }

        getTriggers();
    </script>
</body>

</html>
	function doGet(e) {
	return HtmlService.createTemplateFromFile('html_app').evaluate().setTitle('Injector');
	}

	function getTriggers(source){
	return ScriptApp.getProjectTriggers().map(function(tr){
	return {
	id: tr.getUniqueId(),
	source: tr.getTriggerSource(),
	sourceId: tr.getTriggerSourceId(),
	name: DriveApp.getFileById(tr.getTriggerSourceId()).getName(),
	handler: tr.getHandlerFunction(),
	source: source
	};
	});
	}

	function bindToSheet(id) {
	var functionName = 'createMenu';
	var res = switchTrigger(id, functionName, 'ON_OPEN');
	Utilities.sleep(1000);
	return getTriggers(res);
	}

	function switchTrigger(id, handlerFunction, eventType) {
	var isExist = false;
	var res = {
	deleted: false,
	added: false
	};
	var triggers = ScriptApp.getUserTriggers(SpreadsheetApp.openById(id));
	for (var i = 0; i < triggers.length; i++) {
	if (triggers[i].getHandlerFunction() === handlerFunction && triggers[i].getEventType() === ScriptApp.EventType[eventType]) {
	isExist = true;
	ScriptApp.deleteTrigger(triggers[i]);
	res.deleted = true;
	}
	}
	if (!isExist) {
	var newTrigger = ScriptApp.newTrigger(handlerFunction);
	res.added = true;
	newTrigger.forSpreadsheet(id).onOpen().create();
	}
	return res;
	}
	function run(){
	SpreadsheetApp.getActiveSpreadsheet().toast(new Date(), 'run');
	}

	function run2(){
	SpreadsheetApp.getActiveSpreadsheet().toast(new Date(), 'run2');
	}

	function runCode(codeText){
	return eval(Utilities.formatString('function(){%s}', codeText))();
	}
	function createMenu(){
	SpreadsheetApp.getUi().createMenu('My menu')
	.addItem('run', 'run')
	.addItem('run2', 'run2')
	.addItem('About', 'showAbout')
	.addToUi();
	}

	function showAbout(){
	var userInterface = HtmlService.createTemplateFromFile('html_about').evaluate();
	SpreadsheetApp.getUi().showModalDialog(userInterface, 'About');
	}
	<!DOCTYPE html>
	<html>

	<head>
	<base target="_top">
	<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
	<style>
	#fixed-div {
	position: fixed;
	top: 1px;
	right: 1px;
	}

	.note {
	color: white;
	padding: 6px;
	border-radius: 1px;
	}

	.red {
	background-color: red;
	}

	.green {
	background-color: green;
	}
	</style>
	</head>

	<body>
	<div id="fixed-div"></div>
	<p>This app connects to your spreadsheets by triggers.</p>
	<p><a href="https://gist.github.com/oshliaer/a1f45e5ae8673dbe55b5074288651695" target="_blank">Gist of GitHub</a></p>
	<p>You could run some code from the field below:</p>
	<div>
	<div class="inline form-group">
	<input type="button" id="example1" value="Example #1" onclick="setExample(0)">
	</div>
	<div class="inline form-group">
	<input type="button" id="example2" value="Example #2" onclick="setExample(1)">
	</div>
	<div class="inline form-group">
	<input type="button" id="example3" value="Example #3" onclick="setExample(2)">
	</div>
	</div>
	<textarea style="width: 100%; height: 130px" id="codeText">
	var s = SpreadsheetApp.getActiveSpreadsheet();
	var name = s.getName();
	s.toast(name);
	</textarea>
	<div><input type="button" value="Run code" onclick="runCode()"></div>
	<script>
	var EXAMPLES = [];
	EXAMPLES.push(
	'var s = SpreadsheetApp.getActiveSpreadsheet();\nvar name = s.getName();\ns.toast(name);\nreturn name;'
	);
	EXAMPLES.push(
	'var s = SpreadsheetApp.getActiveSpreadsheet();\nvar value = s.getActiveCell().getDisplayValue();\ns.toast(value, "Active cell value is");\nreturn value;'
	);
	EXAMPLES.push(
	'var s = SpreadsheetApp.getActiveSpreadsheet();\nvar a1Nota = s.getActiveRange().getA1Notation();\ns.toast(a1Nota, "Active Range is");\nreturn a1Nota;'
	);
	var codeText = document.getElementById("codeText");
	var fixedDiv = document.getElementById("fixed-div");

	function runCode() {
	google.script.run
	.withSuccessHandler(function(e) {
	notify(`Success! Result: ${e}`, 'green');
	})
	.withFailureHandler(function(e) {
	notify(e.message, 'red');
	})
	.runCode(codeText.value);
	}

	function notify(text, _class) {
	console.log('notify', text);
	var note = appendNote(text, _class);
	window.setTimeout(function(note) {
	note.remove();
	}, 10000, note);
	}

	function appendNote(text, _class) {
	var currentDiv = document.createElement('div');
	currentDiv.innerHTML = text;
	currentDiv.className += ['note', _class].join(' ');
	fixedDiv.appendChild(currentDiv);
	return currentDiv;
	}

	Element.prototype.remove = function() {
	this.parentElement.removeChild(this);
	}
	NodeList.prototype.remove = HTMLCollection.prototype.remove = function() {
	for (var i = this.length - 1; i >= 0; i--) {
	if (this[i] && this[i].parentElement) {
	this[i].parentElement.removeChild(this[i]);
	}
	}
	}

	function setExample(n) {
	n = (n >= 0 && n <= 3) ? n : 0;
	codeText.value = EXAMPLES[n];
	}

	setExample();
	</script>
	</body>

	</html>