Import from Excel/CSV into a single object grid with copy/paste
This requires a CSS and JS file. Both are shown here. Be sure to note the limitations called out in the comment section at the top of each file.
Type: CSS
/* IMPORTANT: This extension requires 2 files. This is a CSS file. There is JS file too.
* CSS feature works under some conditions which need to be followed -
* Date formatting in Excel must show all 4 year numbers: 2012 (not /12)
* Add both CSS and JS files on the Grid.
* The grid should be a Single Object Grid
* Code does not support 'Owner' field
* Create an Excel Sheet or CSV with API names of the grid fields
* In date fields, be sure to include all 4 digits for the year (such as 2020)
* You can skip any non-required/ optional fields
* Custom code will add a 'Import' button on top
* Click on the Import button and it should launch an import window
* Copy the excel file data along with the field API names and paste it (ctrl+c, ctrl+v)
*Save and it should save the imported record on the grid
*/
#importBox .header {
padding: 5px;
font-size: 12px;
font-weight: bold;
}
#importBox .title {
float: left;
color: #EFEFEF;
font-size: 1.1em
}
#importBox .closeX {
float: right;
padding-top: 0;
padding-right: 0;
color: #EFEFEF;
font-weight: normal;
}
#importBox {
background-color: #0088BA;
}
#importBox #importBtns {
background-color: #F5F5F5;
}
#importBox {
width: 660px;
display: none;
position: absolute;
padding: 5px;
font-size: 13px;
}
#importBox #importBody {
min-height: 80px;
padding: 20px 18px;
background-color: white;
overflow: auto;
}
#importBox #importBtns {
text-align: center;
padding: 12px 20px;
}
#importBtns .importInfo{
text-align: left;
}
#importBox #importBody, #importBox #importBtns {
cursor: default;
}
#importBox.importBox {
cursor: move;
}
#importBox ul.udc {
list-style-type: none;
padding-left: 0;
margin-top: 0;
}
#importBox ul.udc .udcField {
cursor: move;
}
#importBox .objectSection {
margin: 0
}
#importBox .objectSection .objectLabel {
display: inline-block;
margin-bottom: 5px;
}
#importBox {
z-index: 6001 !important;
}
Type: Javascript
/* Import feature works under some conditions which need to be followed -
* Date formatting in Excel must show all 4 year numbers: 2012 (not /12)
* Add both CSS and JS files on the Grid.
* The grid should be a Single Object Grid
* Code does not support 'Owner' field
* Create an Excel Sheet with API names of the grid fields
* You can skip any non-required/ optional fields
* Custom code will add a 'Import' button on top
* Click on the Import button and it should launch an import window
* Copy the excel file data along with the field API names and paste it (ctrl+c, ctrl+v)
* If Excel file import doesn't work, copy the data from Google Spreadsheets
* To update existing records, add "Id" as the *first* column
* Save and it should save the imported record on the grid
*/
var importdata;
jq(document).ready(function () {
init();
});
function init(){
// append button
jq('.gbBtnGroup:eq(1)').after('<span class="gbBtnGroup"><input class="importPopup gbBtn" type="button" value="Import" title="Open import from excel popup"></span>')
// append popup
jq('body').append('<div id="importBox" class="msgBox boxShadow importBox"> <div id="importDiv"> <div class="header"> <span class="title">Import from spreadsheet</span> <span class="closeX">Close <b>X</b></span> <br clear="all"/> </div><div id="importBtns" class="importInfo"> <div>Please paste the spreadsheet data while this popup is open by clicking Cmd + V or Ctrl + V</div><div>If the data contains record ids, the records will be updated, if they don\'t, new records will be created.</div></div><div id="importBody"> <div id="importStats"></div></div><div id="importBtns"> <input type="button" class="gbBtn applyImport" value="Apply"/> <input type="button" class="gbBtn importCancel" value="Cancel"/> </div></div></div>');
jq('#importBox').find('span.closeX, .importCancel').click(function () {
// close the widget
toggleFieldSelectorPopup(false);
});
jq('#importBox .applyImport').click(function () {
applyImport(false);
});
jq('.importPopup').on('click', function(){
toggleFieldSelectorPopup(true);
});
document.addEventListener('paste', handlePaste);
}
function toggleFieldSelectorPopup(pShowBox) {
var importBox = jq('#importBox');
if (pShowBox) {
jq('#importStats').empty();
importBox.find('.applyImport').hide();
// center and show the widget, and show the background overlay
jq('#gbOverlay').show();
importBox.center();
importBox.show();
} else {
// hide the widget and overlay
importBox.hide();
jq('#gbOverlay').hide();
}
}
function handlePaste (e) {
var clipboardData, pastedData;
// Stop data actually being pasted into div
e.stopPropagation();
e.preventDefault();
// Get pasted data via clipboard API
clipboardData = e.clipboardData || window.clipboardData;
pastedData = clipboardData.getData('Text');
// parse pasted data to a table
var rows = pastedData.split(/\r\n|\n|\r/);
for (var i = 0; i < rows.length; i++) {
rows[i] = rows[i].split('\t');
}
// remove last empty row on Windows paste
if (rows[rows.length -1] !== undefined && rows[rows.length -1].length === 1 && rows[rows.length -1][0] === '') {
rows.splice(-1,1);
}
importdata = {
columns: rows.shift(),
data: rows
};
renderImportStats();
}
function renderImportStats(){
var hasIds = importdata.columns.some(function(item){
if (item.toLowerCase() == 'id') return true;
});
var statsText = 'The pasted data will ' + (hasIds?'update':'create') + ' ' + importdata.data.length + ' records. Would you like to apply these changes?'
jq('#importBox .applyImport').show();
jq('#importStats').empty().append('<span>'+statsText+'</span>')
}
function applyImport(){
var hasIds = importdata.columns.some(function(item){
if (item.toLowerCase() == 'id') return true;
});
// reverse the order of data so that it looks like the excel sheet
importdata.data.reverse();
if(hasIds){
editRecords();
}else{
createNewRecords();
}
toggleFieldSelectorPopup(false);
}
function createNewRecords() {
var importRow;
var newRow;
for (var i = 0; i < importdata.data.length; i++) {
importRow = importdata.data[i];
newRow = insertNewRow(getDataTable('p'));
applyRowData(newRow, importRow);
}
}
function editRecords(){
for (var i = 0; i < importdata.data.length; i++) {
importRow = importdata.data[i];
applyRowData(null, importRow);
}
}
function applyRowData(rowElem, rowData){
var colVal;
var newCol;
var metaCol;
for (var j = 0; j < importdata.columns.length; j++) {
// if column is id, find row
if(importdata.columns[j].toLowerCase() == 'id'){
rowElem = jq('#'+rowData[j]);
continue;
}
// fill in column with data
metaCol = getMetaColByFieldName(gridInfoMap.p, importdata.columns[j].trim());
colVal = rowData[j];
newCol = rowElem.find('td[name="' + metaCol.fieldId + '"]');
// data type specific handling
if(metaCol.colDataType == 'PICKLIST'){
newCol.find('.plTxt').click();
}
newCol.find('input, select, textarea').val(colVal).change();
}
}
