Menu
I have a google spreadsheet, where some rows append on daily basis and using the google spreadsheet, the customer feedback team follows up.
Google Spreadsheet Data.
https://docs.google.com/spreadsheets/d/1V-XZdCUZAQVkfCat9vXVxITjjNMxNMPDin6B5j9uMWY/edit?usp=sharing
The above mentioned Google Spreadsheet always have the below mentioned data at google sheet (Highlighted in blue):
Ref ID Company Name Contact No.1 Contact No.2 Project Name Agent ID
Rest of the mentioned details would be captured from the HTML UI basis the user response and finally click on 'Submit & Next' or 'Next' the input get stored at google sheet.
The User has to first enter the 'Agent Id' on HTML UI and accordingly one by one Ref ID detail would be given to particular 'Agent Id` user.
As mentioned in the attached screenshot, The left side of the information would be static as per the googlespread sheet, and right hand side information would be filled by the user basis the telephonic conversation.
Below mentioned particulars will be drop down or radio options basis user input:
Product : Lite, Lite-I, Elite
Ref Code: LIT-1, LIT-2, LIT-3
Status : Accept, Reject, Pending
Comment : Satisfied, Call Back, Pending
Below mentioned particulars will be derived:
Days Passed: It will be derived from the current system year - year mentioned in the `Date`
Below mentioned particulars will be user input as a free text.
Client Name
Notes
Final_Status
Note: The agents will be assigned and shown only those Ref ID where the Agent ID is not blank and Final_Status is either blank or other than 'Submit & Next' marked in Googlespread Sheet.
We need to add one more column in the Googlespread sheet, Which capture the Date time stamp as per the system date as soon as the Final_Status marked as 'Submit & Next` or 'Next'
Submit & Next button would only be enable if all the details are captured by user.
Next Button would only be enable if Comment option is selected.
Also, If there is no new rows available in the googlesheet for the data entry using UI, the UI will throw the message to User that there is 'No New task available' on a blank screen by clicking on 'Submit & Next' or 'Next' button.
Expected UI:
771
Right click in Google Sheet Automation folder and then select Google Sheets -> Blank spreadsheet from the pop-up menu. Once you will click on Blank spreadsheet, it will create and open blank spreadsheet in new tab of the browser. 6. Rename the spreadsheet with ‘Employee Data Entry Form’.
Step 1 — Open your Google Sheets spreadsheet and create a Google Form to enter data into it. Step 2 —Write Apps Script code to embed the Google Form in your Google Sheets spreadsheet. Step 3 — Test entering data from the embedded Google Form to confirm that everything works correctly.
If you have a simple app and you don’t need a very complex backend, you can use a Google spreadsheet to store your data. What is a database in our backends? It’s a table, and Google spreadsheets are also tables. Let’s see how we can use these tables as a backend for our apps.
Google provides a default data entry form that is a Google Form. It can be utilized for a basic data entry job. When it comes to complex and large scale of data entry works, it is always preferable to develop an Automated Data Entry Form to make the task user friendly and improve accuracy & speed.
It looks like Google Apps Developer docs have a decent guide for something similar to this:
Useful for building web apps or adding custom user interfaces in Google Docs, Sheets, and Forms.
https://developers.google.com/apps-script/guides/html
Looks like you need to grant Google Apps Scripts access, then add one:
https://developers.google.com/apps-script/guides/standalone
You can create a standalone script, or manually connect your project:
Go to Google Drive and click New > More > Connect more apps.
When the "Connect apps to Drive" window appears, type "script" into the search box and press Enter.
Click Connect next to the listing for Google Apps Script.
Google's script example says your scripts would look something like this:
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Dialog')
.addItem('Open', 'openDialog')
.addToUi();
}
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('Index');
SpreadsheetApp.getUi()
.showModalDialog(html, 'Dialog title');
}
with the corresponding HTML:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
My Google Sheets Interface.
<input type="button" value="Close"
onclick="google.script.host.close()" />
</body>
</html>
There seems to be good documentation here:
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app
so I'd say that's a good place to start. What you're trying to achieve looks doable for sure, I think it'll be a matter of tweaking it to what google scripts supports.
Good luck!!
60
Data Entry Dialog Created from Header Info on Spreadsheet
Code.gs:
function onOpen() {
SpreadsheetApp.getUi().createMenu("My Menu")
.addItem('Launch Dialog','launchTheFormAsDialog')
.addToUi();
}
function buildForm() {
var searchColumnName='RefId';
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Sheet1');
var tA=sh.getRange(1,1,1,sh.getLastColumn()).getValues()[0];
var hA=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0];
tA.splice(1,5);
var ftA=tA.slice();
hA.splice(1,5);
var fA=hA.slice();
var dstr=Utilities.formatDate(new Date(),Session.getScriptTimeZone(), "yyyy-MM-dd");
var html='<style>input{margin:2px 5px 2px 0;}</style><form id="myForm">';
for(var i=0;i<fA.length;i++) {
switch(ftA[i]){
case 'date':
html+=Utilities.formatString('<br /><input type="%s" value="%s" name="%s" /> %s',ftA[i],dstr,fA[i],fA[i]);
break;
default:
html+=Utilities.formatString('<br /><input type="%s" name="%s" /> %s',ftA[i],fA[i],fA[i]);
break;
}
}
html+='<br /><input type="button" value="Submit" onclick="submitForm(this.parentNode)" /></form>';
return {html:html};
}
function testUpload() {
upload({'Status':'none', 'Comment':'to long to fit', 'ClientName':'Don Trump', 'RefCode':'Tweeter', 'Final_Status':'impeachment', 'Product':'Bullshit', 'RefId':'id3', 'DaysPassed':'12', 'Final_Status_Date':'2019-12-23', 'Date':'2019-12-23', 'Notes':'none'})
}
function upload(theForm) {
Logger.log(theForm);
var kA=Object.keys(theForm);
kA.splice(kA.indexOf('refId'),1);//remove refID
Logger.log(kA);
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Sheet1');
var hA=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0];
var hObj={};
hA.forEach(function(e,i){hObj[e]=i+1});
Logger.log(hObj);
var vA=sh.getRange(3,1,sh.getLastRow()-2,2).getValues();
for(var i=0;i<vA.length;i++) {
if(theForm.RefId==vA[i][0]) {
kA.forEach(function(key){
Logger.log(hObj[key]);
Logger.log(theForm[key]);
sh.getRange(i+3,hObj[key]).setValue(theForm[key]);
});
}
}
return buildForm();
}
function launchTheFormAsDialog() {
var ui=HtmlService.createHtmlOutputFromFile('theform').setHeight(550);
SpreadsheetApp.getUi().showModelessDialog(ui, "Form Data Entry");
}
theform.html:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$(function() {
$(function(){
google.script.run
.withSuccessHandler(function(obj){
$('#formDiv').html(obj.html);
})
.buildForm();
});
});
function submitForm(frmData) {
google.script.run
.withSuccessHandler(function(obj){
//console.log('flag1');
$('#formDiv').html(obj.html);
})
.upload(frmData);
}
function updateSelect(vA,id){
var id=id || 'sel1';
var select = document.getElementById(id);
select.options.length = 0;
for(var i=0;i<vA.length;i++) {
select.options[i] = new Option(vA[i][1],vA[i][0]);
}
}
console.log('My Code');
</script>
</head>
<body>
<h1 id="main-heading">Form Data Entry</h1>
<div id="formDiv"></div>
</body>
</html>
My Spreadsheet:
I added input data types so that I could initialize the date fields and so that the form could be completely built from data on the Spreadsheet even if columns are added or moved around. You can always hide that row since it's on the top.
The Dialog:
2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
