I am trying to paste a text in a textarea and paste it in a sheet, like a Ctrl+shift+V command. The following script only paste in one cell, but I want to paste the way that span in the sheet (like copy and paste in Ctrl+shift+v).
Here comes the html sample file: teste.html
<!DOCTYPE html>
<html>
<!-- Document Head -->
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<base target="_top">
<!-- Add the Google Apps Script CSS file -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="/resources/demos/style.css">
<script src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"> </script>
<script>
$( function() {
$( "#tabs" ).tabs();
} );
</script>
<!-- Add Styling to your sidebar -->
<!-- You can also refer to an external stylesheet - as with the link above or other css frameworks like Bootstrap or W3School's CSS -->
<!-- Try not to have styling elements within your html page and rather make of use external stylesheets -->
<style>
body {
padding-left: 10px;
}
a:active {
color: white;
text-decoration: none:
}
a:hover {
color: white;
text-decoration: none:
}
a:link {
color: white;
text-decoration: none:
}
a:visited {
color: white;
text-decoration: none:
}
div {
padding: 3px;
}
</style>
</head>
<!-- Document Body -->
<body>
<h2>Colar dados do GDL</h2>
<form id="myform">
<div>
<textarea rows="150" cols="10" id = "textareagdl" style="width:200px;height:150px;"></textarea>
</div>
<div>
<button type="button"onclick="myFunction()">Copy</button>
<p id="demo"></p>
<script>
function myFunction() {
var x = document.getElementById("textareagdl").value;
google.script.run.colargdl(x);
}
</script>
</div>
</form>
</body>
</html>
Here comes the google-script file: codigo.gs
function onOpen() {
showSidebar();
}
function showSidebar() {
var html = HtmlService.createHtmlOutputFromFile('teste')
.setTitle('Create')
.setWidth(300);
SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
.showSidebar(html);
}
function colargdl(dadoscsv){
var ss = SpreadsheetApp.getActive();
ss.getSheetByName('teste').getRange('A1:J120').clear();
var sheet = ss.getSheetByName('teste').getRange('A1').setValue(dadoscsv);
//dadoscsv.copyTo(sheet, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
//values.copyTo(sheet.getRange("A1"), {contentsOnly:true});
}
Regards
In Google Apps Script you can call
range.setValues()to fill in more than one cell at a time.setValuestakes a two-dimensional array of values. You will have to convert the contents of the text area into a two-dimensional array. Once that is done you can callrange.setValues()You need to get a range that matches the dimensions of your array -
sheet.getRange(row, col, numRows, numCols)does this: