Created
July 16, 2020 12:18
-
-
Save PolarBearGG/213ecdf7d5425d9cdc5aa8e28610a471 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
const PREFIX = 'prefix'; | |
const EXTERNAL_ID = 'docId'; | |
const TAB_NAME = 'tabName'; | |
const LOCAL_NAME = 'localTabName'; | |
const RANGE = false; | |
var documentProperties = PropertiesService.getDocumentProperties(); | |
function importFromDoc(prefix = false, externalDocumentId = false, tabName = false, localTab = false, range = false) { | |
if (!prefix) { | |
prefix = setVariable("prefix"); | |
if (!prefix) { | |
return false; | |
} | |
} | |
if (!externalDocumentId) { | |
externalDocumentId = setVariable("External Document ID"); | |
if (!externalDocumentId) { | |
return false; | |
} | |
} | |
if (!tabName) { | |
tabName = setVariable("Tab to import data"); | |
if (!tabName) { | |
return false; | |
} | |
} | |
if (!localTab) { | |
localTab = setVariable("Tab in this document", false); | |
} | |
if (!range) { | |
range = setVariable("Range"); | |
} | |
documentProperties.setProperty(prefix + EXTERNAL_ID, externalDocumentId); | |
documentProperties.setProperty(prefix + TAB_NAME, tabName); | |
documentProperties.setProperty(prefix + LOCAL_NAME, localTab); | |
documentProperties.setProperty(prefix + RANGE, range); | |
fetchExternal(externalDocumentId, tabName, range, localTab); | |
} | |
function setVariable(name, required = true) | |
{ | |
var value = false; | |
var ui = SpreadsheetApp.getUi(); | |
var result = ui.prompt( | |
`Set ${name}`, | |
'value', | |
ui.ButtonSet.OK | |
); | |
let close = false; | |
var button = result.getSelectedButton(); | |
var text = result.getResponseText(); | |
if (button == ui.Button.OK) { | |
value = text; | |
} else if (button == ui.Button.CLOSE) { | |
close = true; | |
} | |
if (!required) { | |
close = true; | |
} | |
while (!close && !value) { | |
value = setVariable(name); | |
} | |
return value; | |
} | |
function fetchExternal(externalDocumentId, tabName, range, localTab) { | |
let spreadsheet = SpreadsheetApp.openById(externalDocumentId); | |
let tab = spreadsheet.getSheetByName(tabName); | |
let spreadsheet2 = SpreadsheetApp.getActiveSpreadsheet(); | |
let tab2 = false; | |
if (localTab) { | |
tab2 = spreadsheet2.getSheetByName(localTab); | |
} else { | |
tab2 = SpreadsheetApp.getActiveSheet(); | |
} | |
let values = tab.getRange(range).getValues(); | |
let rows = values.length; | |
let cells = values[0] ? values[0].length : 0; | |
let localRange = tab2.getRange(1, 1, rows, cells); | |
tab2.clear(); | |
localRange.setValues(values); | |
} | |
function opapa() { | |
importFromDoc("test", "tableID", "blocked list", "test", "A1:B5"); | |
} | |
function onOpen() { | |
let sheet = SpreadsheetApp.getActiveSpreadsheet(); | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu('Imports') | |
.addItem('Import from document', 'importFromDoc') | |
.addSeparator() | |
.addItem('Test, predifined data', 'opapa') | |
.addToUi(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment