Data / Design Project 1 - Automated Bulk Entry
Needing a way to automate the bulk-entry of records and keeping data formatted for deep analysis, this sheet & script was created. Filling out the various forms, and using the ‘Amount Before’ and ‘Amount After’ to calculate the total supplies distributed, allows one to bulk enter this data by simply clicking “Submit”. They may then use the various tabs at the bottom to analyze the inputted data, and view deeper analysis such as of the promotional items, or of the inventory monetary income/outcome.
Use the embed and code provided below to look at the completed data tracking sheet.
Or, use this template to create a copy.
(You may need to grant permission to run the script).
CODE USED FOR THE SUBMIT AND CLEAR BUTTONS:
* Global variables
*/
var choice = Boolean;
/**
* Displays confirmation window.
*/
function showAlert() {
var ui = SpreadsheetApp.getUi(); // Same variations.
var result = ui.alert(
“Please confirm”,
“Are you sure you want to continue?”,
ui.ButtonSet.YES_NO,
);
if (result == ui.Button.YES) {
// User clicked “Yes”.
choice = true;
} else {
// User clicked “No” or X in the title bar.
choice = false;
}
}
function ClearCells() {
var sheet = SpreadsheetApp.getActive().getSheetByName(‘PAR Sheet’);
showAlert()
if (choice == true){
sheet.getRange(‘C3:C26’).clearContent();
sheet.getRange(‘D3:D26’).clearContent();
sheet.getRange(‘J10:J15’).clearContent();
SpreadsheetApp.getActiveSpreadsheet().getRange(“A23:A26”).setValue(“-“);
SpreadsheetApp.getActiveSpreadsheet().getRange(“B23:B26”).setValue(“#”);
}
else {
return;
}
}
/**
* Submits all data in both inputs.
*/
function SubmitCells() {
const sheet = SpreadsheetApp.getActiveSpreadsheet();
// Collect the data.
const itemRange = sheet.getRangeByName(“PARitem”);
const itemVals = itemRange.getValues().flat();
const upcRange = sheet.getRangeByName(“PARupc”);
const upcVals = upcRange.getValues().flat();
const quantityRange = sheet.getRangeByName(“PARquantity”);
const quantityVals = quantityRange.getValues().flat();
const dateRange = sheet.getRangeByName(“PARdate”);
const dateVals = dateRange.getValues().flat();
const locationRange = sheet.getRangeByName(“PARlocation”);
const locationVals = locationRange.getValues().flat();
const nameRange = sheet.getRangeByName(“PARname”);
const nameVals = nameRange.getValues().flat();
const staffRange = sheet.getRangeByName(“PARstaff”);
const staffVals = staffRange.getValues().flat();
const restockRange = sheet.getRangeByName(“PARrestock”);
const restockVals = restockRange.getValues().flat();
const notesRange = sheet.getRangeByName(“PARnotes”);
const notesVals = notesRange.getValues().flat();
// Validate all required cells are filled.
const anyEmpty1 = dateVals.findIndex(cell => cell == “”);
const anyEmpty2 = locationVals.findIndex(cell => cell == “”);
if(anyEmpty1 !== -1){
const ui = SpreadsheetApp.getUi();
ui.alert(
“Input Incomplete”,
“Check labels below instructions.”,
ui.ButtonSet.OK
);
return;
};
if(anyEmpty2 !== -1){
const ui = SpreadsheetApp.getUi();
ui.alert(
“Input Incomplete”,
“Check labels below instructions.”,
ui.ButtonSet.OK
);
return;
};
// Append the data.
showAlert()
if (choice == true){
for (i = 0; i < quantityVals.length; i++){
const data = [itemVals[i],upcVals[i],quantityVals[i],dateVals[0],locationVals[0],nameVals[0],staffVals[0],restockVals[0],notesVals[0]];
const input = sheet.getSheetByName(“Data”);
if (quantityVals[i] != 0){
console.log(data);
input.appendRow(data);
}
}
if (i >= quantityVals.length){
sheet.getRange(‘C3:C26’).clearContent();
sheet.getRange(‘D3:D26’).clearContent();
sheet.getRange(‘J10:J15’).clearContent();
SpreadsheetApp.getActiveSpreadsheet().getRange(“A23:A26”).setValue(“-“);
SpreadsheetApp.getActiveSpreadsheet().getRange(“B23:B26”).setValue(“#”);
sheet.toast(“Success: PAR Sheet added to Input!”);
}
}
else {
return;
}
}