I've got a spreadsheet I'm trying to develop and could use a bit of help with it.
A quick summary of the sheet, is it contains cases/items than need human attention/working.
Before it gets to the human, it's very important any postcode is correct. So on the form feeding the sheet, I have a regular expression that ensures postcodes are in the right format/correct chars etc.
This doesn't prevent all errors, So in the sheet itself, I have a vlookup that checks postcode and if a match isn't found, Incorrect Postcode is added elsewhere in the sheet (Column BN), and we know that item needs fixing.
This still isn't ideal, as it requires a human to find all the "Incorrect Postcode" entries and correct the postcode. These could be hidden/dotted about in a sheet of 2000+ entries.
So my next step was to create a script that looks for the "Incorrect Postcode" value, and logs the line of the sheet it's found on. Making it easier to find and fix. It also tells you the total amount of errors.
Here is the code:
function postcodeFix() {
var sourceSheet = "Form Responses";
var postcodeError = "Incorrect Postcode";
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(sourceSheet);
var values = sheet.getRange("BN3:BN").getValues();
var errorArray = [];
for (i = 0; i < values.length;i++) {
if (values[i] == postcodeError)
{
errorArray.push(i+3);
}
}
if (errorArray.length <= 0)
{
Browser.msgBox("No Errors Found","All postcodes are correct, no errors found.",Browser.Buttons.OK);
}
else
{
Browser.msgBox("Errors Found","There are currently " + errorArray.length + " Postcode errors that need correcting. " + " The errors are located on the following lines of the 'Form Responses' sheet: "+errorArray+
". Would you like to fix these errors now?", Browser.Buttons.YES_NO);
}
}
After finishing this part of the project, it got me thinking, using the resulting data from the script.
Could that somehow feed a form / ui. That loads up the incorrect entries, allowing you to correct them via the form / ui rather than on the spreadsheet itself.
I'm not an expert at Javascript, but think I can work out how to manage the prompt responses, YES.NO etc. Just not sure on how to create or populate form / ui.
Has anyone done anything similar to this before, or got any handy links or pointers?
You can use a Modal Dialog box. The Dialog can include custom HTML.
Make an onOpen() function
- Click the TOOLS menu
- Click SCRIPT EDITOR
Add this Code:
// This will run when the spreadsheet is opened or the browser page is refreshed
function onOpen() {
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.createMenu('Custom Menu')
.addItem('Open Dialog Box', 'openDialog')
.addToUi();
}
That onOpen() function will run when the spreadsheet opens.
Create a function that will run when the menu item is chosen
In a .gs script file, add this function.
function openDialog() {
var html = HtmlService.createHtmlOutputFromFile('index')
.setSandboxMode(HtmlService.SandboxMode.NATIVE);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.showModalDialog(html, 'Put a Title Description HERE');
}
Create an index.html file
In the Apps Script code editor,
Choose:
Enter this HTML:
<br>
The NEW Zip: <input type="text" />
<br>
<br>
<input type="button" value="Get The Information" onclick='injectSomeText()'/>
<br>
<br>
<input type="button" value="Get Sheet Data" onclick='getSheetData()'/>
Here is your information!
<div id='myZipInfo'></div>
<br><br>
<input type="button" value="Close"
onclick="google.script.host.close()" />
<script>
window.injectSomeText = function() {
console.log('it ran');
document.getElementById('myZipInfo').textContent = 'This is test text. It worked!';
};
window.onSuccess = function(returnedData) {
document.getElementById('myZipInfo').textContent = returnedData;
}
window.getSheetData = function() {
google.script.run.withSuccessHandler(onSuccess)
.readSheetData();
}
</script>
Save the file with a name of "index"
Now add some more code to the Code.gs file.
Code.gs
function readSheetData() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 0; i < data.length; i++) {
Logger.log('Column One: ' + data[i][0]);
Logger.log('Column Two: ' + data[i][1]);
}
return data;
}
Save the Code.gs file and
Refresh the browser window with the Google spreadsheet, and the onOpen() function will run.
This example shows the basics of putting HTML, a button and an input field in a Modal Dialog.
You could create a button named: Fixed Post Code, and make it run your postcodeFix() function.
<input type="button" value="Fixed Post Code" onclick="google.script.run.postcodeFix()"/>
You'll still need to configure the code to retrieve data from the correct row and columns of your spreadsheet, and figure out the best way to inject that information into the HTML. You may want to configure the data as an array or an object or some combination of the two.
This example shows that HTML can be injected into the Dialog Box. Click the "Get the Information" button, and text will appear in the Dialog Box.
Note that there is an HTML SCRIPT tag in the HTML of the index.html file. You can actually add client side JavaScript and have it run in a Modal Dialog box.
Here is a solution particular to your spreadsheet:
function readSheetData() {
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getDataRange().getValues();
var lookup = "Incorrect Postcode";
var data = [];
for (var i = 0; i < values.length; i++) {
Logger.log(values[i][65]);
if (values[i][65] == lookup){
data.push(values[i][3]); //Push Zip Code to Array
data.push(i+1); //Push Row Number to array which is same as iteration number + 1
}
};
Logger.log(data)
return data;
}
New HTML
<style>
table, td {
border: 1px solid black;
}
</style>
<input type="button" value="Get Bad ZIPs" onclick='getSheetData()'/>
<br>
<br>
<div id='msgInfo' style='display:none'> Here is your information! </div>
<br>
<table id="myTable">
<th>Zip Code</th>
<th>Row Number</th>
<th>New ZIP</th>
</table>
<br>
<br>
<input type="button" value="Close"
onclick="google.script.host.close()" />
<script>
window.onSuccess = function(returnedData) {
document.getElementById('msgInfo').style = 'display:inline';
// Find a <table> element with id="myTable":
var table = document.getElementById("myTable");
console.log(returnedData.length);
for (var i=0; i < returnedData.length; i = i+2) {
console.log("i: " + i);
console.log("returnedData[i]: " + returnedData[i]);
// Create an empty <tr> element and add it to the 1st position of the table:
var row = table.insertRow(i/2);
// Insert new cells (<td> elements) at the 1st and 2nd position of the "new" <tr> element:
var cell1 = row.insertCell(0);
var cell2 = row.insertCell(1);
var cell3 = row.insertCell(2);
// Add some text to the new cells:
cell1.innerHTML = returnedData[i];
cell2.innerHTML = returnedData[i+1];
var element1 = document.createElement("input");
element1.type = "text";
element1.name = "txtbox[]";
cell3.appendChild(element1);
};
}
window.getSheetData = function() {
google.script.run.withSuccessHandler(onSuccess)
.readSheetData();
}
</script>
