google-apps-script - 除了 OnFormSubmit,有没有办法从 Google 表格中获取一行以使用 AppsScript 与 Google Doc 合并?
问题描述
我知道如何使用触发器 OnFormSubmit 将数据从 Google 表单提交到 Google 表格合并到 Google 文档。
我想知道有没有办法从谷歌表格中获取一行数据并与相同或相似的谷歌文档合并而不通过表单?
我仍在学习 AppScripts,看起来很容易,但我无法通过谷歌搜索找到任何东西。
我知道我需要指定活动行等,然后我需要弄清楚如何从活动行中获取我的 e.values 或其他变量。
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getActiveRow = [{name: "getActiveRow", functionName: "fetchActiveRow"}];
ss.addMenu("customMenu", getActiveRow);
};
function fetchActiveRow(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var row = ss.getActiveSheet().getActiveRange().getRow();
我希望获取行数据并与 Google 文档合并,以便在适当的空间填写“纸质表格”。
任何帮助将不胜感激。
解决方案
一个简单的谷歌文档信封打印机
那么这是一个简单的 10 号信封打印机。该代码位于 Google 文档中,但它访问电子表格以存储地址。您可以在边栏中输入地址,也可以简单地将它们输入电子表格中,我个人觉得这更容易。
信封打印机.gs:
var DSSID=getDSSID();
function onOpen(e)
{
makeEnvelopeMenu()
}
function onInstall(e){
setupEnvelopeApp();
setupDocument();
onOpen(e);
}
function makeEnvelopeMenu()//Rename this to onOpen if this is all you have in this document
{
DocumentApp.getUi().createMenu('Envelope Printer')
.addItem('Show Envelope Sidebar', 'displayEnvelopeSidebar')
.addSubMenu(DocumentApp.getUi().createMenu('Initial Setup')
.addItem('Initialize Spreadsheet Id for Email Address', 'setupEnvelopeApp')
.addItem('Setup Document', 'setupDocument') )
.addToUi();
}
function setupEnvelopeApp()//To make the connection between this document and the spreadsheet you created to store mailing addresses run this script from Script Editor
{
var resp=DocumentApp.getUi().prompt('Mailing Address SpreadsheetId', 'Create a spreadsheet to store mailing address and enter the id of that spreadsheet here', DocumentApp.getUi().ButtonSet.OK);
var dssid=resp.getResponseText();
var props=PropertiesService.getScriptProperties();
props.setProperty('DSSID', dssid);
var ss=SpreadsheetApp.openById(getDSSID());
var sh=ss.getSheetByName('Addresses');
if(!sh) {
var sh=ss.insertSheet('Addresses');
}
sh.appendRow(['Line 1','Line 2','Line 3','Line 4']);
sh.getRange(1,1,1,4).setFontWeight("Bold");
}
function displayDSSID(){
DocumentApp.getUi().alert('The Id of the spreadsheet that stores emails is ' + getDSSID());
}
function resetDSSID() {
PropertiesService.getScriptProperties().setProperty('DSSID', '');
}
function getDSSID(){
return PropertiesService.getScriptProperties().getProperty('DSSID');
}
function setupDocument(){
var doc=DocumentApp.getActiveDocument();
var envelope10={};
envelope10[DocumentApp.Attribute.PAGE_HEIGHT]=296;
envelope10[DocumentApp.Attribute.PAGE_WIDTH]=684;
envelope10[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
envelope10[DocumentApp.Attribute.FONT_SIZE] = 14;
envelope10[DocumentApp.Attribute.BOLD] = true;
envelope10[DocumentApp.Attribute.LINE_SPACING]=1;
envelope10[DocumentApp.Attribute.MARGIN_LEFT]=36;
envelope10[DocumentApp.Attribute.MARGIN_RIGHT]=36;
envelope10[DocumentApp.Attribute.MARGIN_TOP]=36;
envelope10[DocumentApp.Attribute.MARGIN_BOTTOM]=36;
doc.getBody().clear().setAttributes(envelope10);
}
function insertReturnAddress(retaddr){
var retaddr=(typeof(retaddr)!='undefined')?retaddr:'No return address selected.';
var retAddrStyle={};
retAddrStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
retAddrStyle[DocumentApp.Attribute.FONT_SIZE] = 14;
retAddrStyle[DocumentApp.Attribute.BOLD] = true;
retAddrStyle[DocumentApp.Attribute.LINE_SPACING]=1;
retAddrStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT]=DocumentApp.HorizontalAlignment.LEFT;
retAddrStyle[DocumentApp.Attribute.MARGIN_TOP]=0;
var doc=DocumentApp.getActiveDocument();
doc.getBody().getChild(0).asParagraph().setAttributes(retAddrStyle).setText(retaddr);
}
function insertRecipientAddress(recaddr){
var recaddr=(typeof(recaddr)!='undefined')?recaddr:'No Recipient Address selected.';
var retAddrStyle={};
retAddrStyle[DocumentApp.Attribute.FONT_FAMILY] = 'Calibri';
retAddrStyle[DocumentApp.Attribute.FONT_SIZE] = 14;
retAddrStyle[DocumentApp.Attribute.BOLD] = true;
retAddrStyle[DocumentApp.Attribute.LINE_SPACING]=1;
retAddrStyle[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT]=DocumentApp.HorizontalAlignment.CENTER;
retAddrStyle[DocumentApp.Attribute.MARGIN_TOP]=0;
var doc=DocumentApp.getActiveDocument();
doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
doc.getBody().appendParagraph('').setAttributes(retAddrStyle);
doc.getBody().appendParagraph(recaddr).setAttributes(retAddrStyle);
}
function prepareEnvelope(retaddr,recaddr){
setupDocument();
insertReturnAddress(retaddr);
insertRecipientAddress(recaddr)
}
function displayEnvelopeSidebar(){
var userInterface=HtmlService.createHtmlOutputFromFile('Envelope').setWidth(300).setHeight(500).setTitle('Printing Envelopes');
DocumentApp.getUi().showSidebar(userInterface);
}
function savAddress(addr){
var ss=SpreadsheetApp.openById(DSSID);
var sh=ss.getSheetByName('Addresses');
if(sh.appendRow(addr))
{
return true;
}
else
{
return false;
}
}
function getAllAddresses(){
var ss=SpreadsheetApp.openById(DSSID);
var sh=ss.getSheetByName('Addresses');
var rg=sh.getRange(2,1,sh.getLastRow(),sh.getLastColumn());
var vA=rg.getValues();
return vA;
}
信封.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<style>
#Instructions{padding:4px;}
</style>
<script>
$(function() {
clearAddressFields();
google.script.run
.withSuccessHandler(putNamesAndAddresses)
.getAllAddresses();
google.script.run
.withSuccessHandler(function(ssid){
document.getElementById('Instructions').innerHTML='Insert Envelope into Printer with front facing up and top facing to the left when printer is in front of you. Folded portion down. Use the Com-10 setting for page size.<br /><span style="font-size:8px;"><strong>SSID:</strong>'+ ssid +'</span>'
})
.getDSSID();
});
function putNamesAndAddresses(data)
{
var select1=document.getElementById("sel1");
var select2=document.getElementById("sel2");
var lf='\n';
select1.options.length=0;
select2.options.length=0;
for(var i=0;i<data.length;i++)
{
var name=data[i][0];
var addr='';
for(var j=0;j<data[i].length;j++)
{
if(j>0 && data[i][j]){addr+=lf;}
addr+=data[i][j];
}
select1.options[i]=new Option(name,addr);
select2.options[i]=new Option(name,addr);
}
}
function saveAddress()
{
$('.addrinp').css('background-color','#ffff00');
var line0=$('#adr0').val();
var line1=$('#adr1').val();
var line2=$('#adr2').val();
var line3=$('#adr3').val();
var addr=[line0,line1,line2,line3];
google.script.run
.withSuccessHandler(clearAddressFields)
.savAddress(addr)
}
function clearAddressFields()
{
$('.addrinp').css('background-color','#ffffff');
$('#adr0').val('');
$('#adr1').val('');
$('#adr2').val('');
$('#adr3').val('');
google.script.run
.withSuccessHandler(putNamesAndAddresses)
.getAllAddresses();
}
function prepareEnvelope()
{
var recaddr=$('#sel1').val();
var retaddr=$('#sel2').val();
google.script.run.prepareEnvelope(retaddr,recaddr);
}
console.log('My Code');
</script>
</head>
<body>
<div id="Instructions"></div>
<div id="envprep" style="border-style: double;padding:0 0 0 10px;">
<h3>Recipient Address:</h3>
<select id="sel1" size="5">
</select>
<h3>Return Address:</h3>
<select id="sel2" size="5">
</select>
<br /><br /><input type="button" id="btn2" value="Address Envelope" onClick="prepareEnvelope();" />
</div>
<div id="newaddr" style="border-style: double;padding:0 0 0 10px;">
<h3>Add Address:</h3>
<input class="addrinp" id="adr0" type="text" size="30" placeholder="1st Line of Address" />
<br /><input class="addrinp" id="adr1" type="text" size="30" placeholder="2nd Line of Address" />
<br /><input class="addrinp" id="adr2" type="text" size="30" placeholder="3rd Line of Address" />
<br /><input class="addrinp" id="adr3" type="text" size="30" placeholder="4th Line of Address" />
<br /><br /><input id="btn1" type="button" value="Save Address" onClick="saveAddress();" />
</div>
<br /><br /><br /><input id="btn0" type="button" value="Close" onClick="google.script.host.close();" />
</body>
</html>
运行初始设置步骤。其中之一是在获取邮寄地址时为电子表格提供文件 ID。它将根据您提供的 ID 打开电子表格,并将创建一个地址表并编写标题行。您所要做的就是通过侧边栏或直接在电子表格上将地址输入该页面。
信封打印机 Google 文档的图像
花一些时间阅读 Google Apps 脚本参考以了解代码中发生了什么。
玩得开心。
推荐阅读
- javascript - 继续循环一个函数,直到里面的回调解析为真
- sql - 获取用户使用特定优惠的次数
- r - 在 R 中安装测试套件中使用的所有软件包?
- sql - Postgresql 多对多表的最佳搜索方式 - 排除和包含标签
- django - 在django的表单的action中设置输入字段的值
- eclipse - 从 Eclipse Selenium Java 中的控制台获取 HTML 中的所有表单标签
- javascript - html数字只有最小值和最大值
- javascript - 带有 jquery 日期时间选择器的 ASP Web 应用程序问题
- powershell - 如何在函数中创建条件参数?
- c# - Xamarin 崩溃:System.MissingMethodException:找不到方法:void .ResourceLoadingQuery.set_Instance(object)