So, I got a google apps script, which is placed on my spreadsheet 'AllScripts'. I'm not the one who wants it like that, but it must be written in a separated spreadsheet.
On the spreadsheet 'wb1' apps scripts I'm importing the code from 'AllScripts' as a library, so I can call its methods on 'wb1'. Still on 'wb1', there is the onOpen method, which creates a menu and add an item with a method from 'AllScripts'. All good 'till here.
The minimal reproducible code is available on below spreadsheets.
These are the 'wb1' spreadsheet and 'AllScripts' spreadsheet, respectively: https://docs.google.com/spreadsheets/d/1uQz8q1AE7vs9eJCajnIAPmQZRQgBoZv1mF9dcH9D_l8/edit?usp=sharing https://docs.google.com/spreadsheets/d/1YaO18yRagzG4wA46AGnzn94CR6Vy6ENZcsbLZKN6OgM/edit?usp=sharing
Note that at first you won't see the menu and can't run any code, so on 'wb1' go to Extensions > Apps Script and then run the onOpen. It will ask you for permissions, and I don't know why, it will open a window with a security warning.
Anyways, when you open the modal from the menu Scripts > Test and write the asked stuffs, open your console on the browser and you should see something like this: [![error][1]][1]
I don't know how to read that, but apparently the code from modal opened on 'wb1' cannot open a method from 'AllScripts'.
How can we overcome this?
These are some code from the script:
ShowForm.gs:
function showForm() {
const form = HtmlService.createTemplateFromFile('Form').evaluate().setHeight(480);
SpreadsheetApp.getUi().showModalDialog(form, 'Request Data Window');
}
SaveData.gs:
function saveData(data) {
const iasRequestsSpreadsheet = SpreadsheetApp.openById('1uQz8q1AE7vs9eJCajnIAPmQZRQgBoZv1mF9dcH9D_l8');
const sheet = iasRequestsSpreadsheet.getSheetByName('page1');
const line = getFirstEmptyRow(sheet);
// request date
sheet.getRange(line, 1).setValue(new Date());
// requested by
sheet.getRange(line, 2).setValue(data.requestedBy);
const currentWorkbook = SpreadsheetApp.getActiveSpreadsheet()
.getName()
.split('-')
.trim();
// product
sheet.getRange(line, 3).setValue(currentWorkbook);
const tabName = SpreadsheetApp.getActiveSheet().getName();
// activity
sheet.getRange(line, 4).setValue(`Do stuffs on ${tabName}`);
// due date
sheet.getRange(line, 6).setValue(data.dueDate);
// status
sheet.getRange(line, 8).setValue('Pending');
// comments
sheet.getRange(line, 9).setValue(data.comments);
}
Form.html: ps.: I removed the Materialize stuffs, so it can be easier to read as suggested by @Rubén.
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<form>
<label for="requestedBy">Requested By</label>
<input id="requestedBy" type="text" placeholder="John Doe">
<label for="dueDate">Due Date</label>
<input id="dueDate" class="datepicker">
<label for="comments">Comments</label>
<textarea id="comments" placeholder="Please ignore lines X, Y and Z..." class="materialize-textarea"></textarea>
<button id="btn">
Submit
</button>
</form>
<script>
document.getElementById('btn').addEventListener('click', getData);
function getData() {
var requestedBy = document.getElementById('requestedBy').value;
var dueDate = document.getElementById('dueDate').value;
var comments = document.getElementById('comments').value;
var data = {
requestedBy: requestedBy,
dueDate: dueDate,
comments: comments
};
google.script.run.saveData(data);
}
</script>
</body>
</html>
