Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Hidden Row Data: A simple Google Add-on that shows the meta data you save to a row in Google Sheets.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Hidden Row Data')
.addItem('Show Data', 'showSidebar')
.addToUi();
}
function showSidebar() {
var html = HtmlService.createTemplateFromFile('sidebar');
SpreadsheetApp.getUi()
.showSidebar(html.evaluate());
}
//Retrives the users data at the selected row
function getUserAtSelectedRow(){
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rowIndex = range.getRow();
range = sheet.getRange(rowIndex + ":" + rowIndex);
var row = range.getValues()[0];
var meta = range.getDeveloperMetadata();
var metaData = "";
meta.forEach(function(m){
if(m.getKey() == "META")
metaData = m.getValue();
})
var user = {
"name": row[0],
"meta": metaData,
"row": rowIndex
}
return user;
}
function save(user){
var range = SpreadsheetApp.getActiveSheet().getRange(user.row + ":" + user.row);
if(range){
var row = range.getValues()[0];
row[0] = user.name;
var metas = range.getDeveloperMetadata();
var metaFound = false;
metas.forEach(function(m){
if(m.getKey() == "META"){
m.setValue(user.meta);
metaFound = true;
}
});
if(!metaFound)
range.addDeveloperMetadata("META", user.meta);
if(row != range.getValues()[0])
range.setValues([row]);
return true;
}
}
//Test Functions
function addDeveloperMeta(){
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getRange("1:1");
range.addDeveloperMetadata("META", "1231231231231321321jhg")
}
function getAllDeveloperMeta(){
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getRange("1:1");
var meta = range.getDeveloperMetadata();
meta.forEach(function(m){
Logger.log(m.getKey() + " : " + m.getValue());
});
}
function removeAllDeveloperMeta(){
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getRange("1:1");
var meta = range.getDeveloperMetadata();
meta.forEach(function(m){
m.remove();
});
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>
<body>
<div class="sidebar">
<h3>Please select a row to edit info</h3>
<p>Click update after you have selected the proper row</p>
<div class="block">
<button onclick="save(getData)">Update</button>
<div class="block">
<h1 id="toast"></h1>
</div>
<? var data = getUserAtSelectedRow(); ?>
<div class="block">
<div class="form-group block">
<label for="name">Row</label>
<input type="text" id="userName" style="width: 100%;" value='<?= data.name ?>'>
</div>
<div class="form-group block">
<label for="name">Hidden Data</label>
<input type="text" id="meta" style="width: 100%;"value='<?= data.meta ?>'>
</div>
</div>
<input id='row' type='hidden' value='<?= data.row ?>'><br>
<button onclick="save(toastSaveSuccess)" class="action">Save</button>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script>
function save(callback){
toastLoading();
var name = document.getElementById("userName").value;
var meta = document.getElementById("meta").value;
var row = document.getElementById("row").value;
var user = {
"name" : name,
"meta" : meta,
"row": row
}
google.script.run.withSuccessHandler(callback).withFailureHandler(toastError).save(user);
}
function getData(){
google.script.run.withSuccessHandler(update).withFailureHandler(toastError).getUserAtSelectedRow();
}
function update(user){
var name = document.getElementById("userName").value = user.name;
var trello = document.getElementById("meta").value = user.meta;
var row = document.getElementById("row").value = user.row;
toastUpdated();
}
function toastLoading(){
var toast = $("#toast");
toast.css("color", "");
toast.text("Saving...");
toast.slideDown();
}
function toastUpdated(){
var toast = $("#toast");
toast.text("Updated");
toast.css("color", "Green");
toast.slideDown();
$("#toast").slideUp(1000);
}
function toastSaveSuccess(){
var toast = $("#toast");
toast.text("Saved");
toast.css("color", "Green");
toast.slideDown();
$("#toast").slideUp(1000);
}
function toastError(){
var toast = $("#toast");
toast.text("Error");
toast.css("color", "Red");
toast.slideDown();
$("#toast").slideUp(1000);
}
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.