CSV Utility

CSV Utility-For copying data to StackOverFlow.com when asking questions

I don't happen to like to follow links to spreadsheets because sometimes they are on shared drives and the links can not be removed from my shared with me section of Google Drive which I personally think is something that Google should fix. In the mean time it's still useful for me to answer your questions to have access to some of the data in your spreadsheets and this routine was designed to produce csv string that you can paste into the SO code block.

The Google Apps Script Code:

//GS Code:

function testrd() {

redactableData({datarange:"A3:Q23",redactrange:"",delimiter:",",redactstring:"xxx"})

}

function redactableData(obj) {

Logger.log(JSON.stringify(obj));

const {datarange,redactrange,delimiter,redactstring}=obj;

const ss=SpreadsheetApp.getActive();

const sh=ss.getActiveSheet();

const drg=sh.getRange(datarange);

const vA=drg.getValues();

if(redactrange) {

const rgA1=redactrange.split(',');

//Logger.log(rgA1);

const rgA=rgA1.map(function(A1,i){

return sh.getRange(A1);

});

const rowStart=drg.getRow();

const colStart=drg.getColumn();

//const rowEnd=drg.getRow()+drg.getHeight()-1;

//const colEnd=drg.getColumn()+drg.getWidth()-1;

rgA.forEach(function(rg,k){

var v=rg.getDisplayValues();

let row=rg.getRow();

let col=rg.getColumn();

v.forEach(function(r,i){

r.forEach(function(c,j){

vA[row-rowStart+i][col-colStart+j]=redactstring;//redact string

});

}

var html='<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"><script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>';

html+='<input type="button" value="Exit" onClick="google.script.host.close();" /><br />';

html+='<textarea rows="1" cols="150" id="rngs"></textarea><br /><body><textarea rows="30" cols="150" id="tsv"></textarea>';

var tsv='';

vA.forEach(function(r,i){if(i>0){tsv+='\\r\\n';}r.forEach(function(c,j){if(j>0){tsv+=delimiter;}tsv+=c;});});

//Logger.log(tsv);

let s=`Data:${datarange} - Redact:${redactrange}`;

html+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" />';

html+=Utilities.formatString('<script>$(function(){$("#tsv").val("%s");$("#rngs").val("%s")});</script></body>',tsv,s);

var userInterface=HtmlService.createHtmlOutput(html).setWidth(1200);

SpreadsheetApp.getUi().showModelessDialog(userInterface, 'TSV')

}

function getCSVDataRange() {

const ss=SpreadsheetApp.getActive();

const sh=ss.getActiveSheet();

const datarange=sh.getActiveRange().getA1Notation();

return {datarange:datarange};

}

function getRedactRangeList() {

const ss=SpreadsheetApp.getActive();

const sh=ss.getActiveSheet();

const rgA=sh.getActiveRangeList().getRanges();

const redactrange=rgA.map(function(rg,i){return rg.getA1Notation();}).join(',');

return {redactrange:redactrange};

}

function showRedactDialog() {

var userInterface=HtmlService.createHtmlOutputFromFile('redactdialog').setWidth(350).setHeight(200);

const h=userInterface.getHeight();

const w=userInterface.getWidth();

const title='Redactable Data';

userInterface.append(Utilities.formatString('<div id="dim">w:%s,h:%s</div>',w,h));

SpreadsheetApp.getUi().showModelessDialog(userInterface, title);

}

function getPresets() {

return {datarange:'',redactrange:'',delimiter:',',redactstring:'Redacted'};

}

//HTML and JAVASCRIPT:

<!DOCTYPE html>

<html>

<head>

<style>

input{margin:2px 5px 2px 0;font-size:12px;}

</style>

</head>

<body>

<br /><input type="text" id="dlm" name="delimiter" size="15" />Delimiter

<br /><input type="text" id="rs" name="redactstring" size="15" />Redact String

</form>

$(function(){

google.script.run

.withSuccessHandler(function(obj){

if(obj.datarange) {$('#dtrg').val(obj.datarange);}

if(obj.redactrange) {$('#rdrg').val(obj.redactrange);}

if(obj.delimiter) {$('#dlm').val(obj.delimiter);}

if(obj.redactstring) {$('#rs').val(obj.redactstring);}

})

.getPresets();

});

function getDataRange() {

google.script.run

.withSuccessHandler(function(obj){

$('#dtrg').val(obj.datarange);

})

.getCSVDataRange();

}

function getRedactRangelist() {

google.script.run

.withSuccessHandler(function(obj){

$('#rdrg').val(obj.redactrange);

})

.getRedactRangeList();

}

function processForm(form) {

google.script.run.redactableData(form);

}

console.log('My Code');

</script>

</body>

</html>

You can run it as dialog maybe put it into a library so that you can easily distribute it to different spreadsheets.

Google Sites

Report abuse