Create a gist now

Instantly share code, notes, and snippets.

@rheajt /Code.gs
Last active Nov 29, 2016

What would you like to do?
filter achieve 3000 data from the sidebar

Achieve 3000 Import

function onOpen() {
SpreadsheetApp.getUi()
.createAddonMenu()
.addItem('Open Sidebar', 'openSidebar')
.addToUi();
}
function onInstall() {
onOpen();
}
function openSidebar() {
return SpreadsheetApp.getUi()
.showSidebar(HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('A3K Import Data'));
}
function importCSV(csv) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
sheet.clear();
var sheetData = sheet.getDataRange().getValues();
var newCSV = Utilities.parseCsv(csv);
newCSV.forEach(function(row, ind) {
if(ind === 0) {
sheet.appendRow([
'Student Name',
'Student Number',
'Activities Completed',
'Total Attempts',
'Success Percentage'
]);
} else {
var studentName = row[4] + ' ' + row[5],
studentNumber = row[6],
activitiesCompleted = +row[14],
totalAttempts = +row[13] + +row[14],
success = Math.floor((activitiesCompleted / totalAttempts) * 100);
sheet.appendRow([studentName, studentNumber, activitiesCompleted, totalAttempts, success]);
}
});
}
<style>
input[type="file"] {
display: none;
}
label {
display: block;
font-size: 1.4em;
border: 1px dashed black;
padding: 10px 20px;
background-color: lightgrey;
}
</style>
<label>
<input type="file" id="a3k-csv" accept=".csv" onchange="changeDisplay()" />
Choose a file
</label>
<p id="file-name"></p>
<button type="click" id="import-csv" onclick="importCSV()">Import</button>
<script>
function changeDisplay() {
var file = document.getElementById('a3k-csv').files[0];
if(file.name) {
document.getElementById('file-name').innerHTML = file.name;
}
}
function importCSV() {
var file = document.getElementById('a3k-csv').files[0];
var reader = new FileReader();
reader.onload = function(e) {
convertCSV(e.target.result);
}
reader.readAsText(file);
}
function convertCSV(csv) {
google.script.run
.withSuccessHandler(closeSidebar)
.withFailureHandler(showError)
.importCSV(csv);
}
function closeSidebar() {
google.script.host.close();
}
function showError(err) {
console.log(err);
}
</script>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment