Google Spreadsheets更新带有多个标签的脚本 [英] Google Spreadsheets updating A Scripts with Multiple Tabs
本文介绍了Google Spreadsheets更新带有多个标签的脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我已经找到了一些关于如何更新脚本的主题，但是我无法使用脚本中的极限知识来操作。
我已达到约6 Tabs，Sheet1，Sheet2，Sheet3，Sheet4，Sheet5，Sheet6，我想发送电子邮件，但现阶段只看第一张。有人可以让我知道如何更新这个请求
谢谢Jarrad
function sendEmails（）{
var sheet = SpreadsheetApp.getActiveSheet（）;
var range = sheet.getRange（1，2）; //获取单元格范围B1：B1
var subject = range.getValues（）; //从上面的范围获取主题行的值
var range = sheet.getRange（1，9）; //获取单元格的范围I1：I1
var numRows = range.getValues（）; //获取超出范围的电子邮件数量值
var startRow = 4; //第一行数据处理
var dataRange = sheet.getRange（startRow，1，numRows，9）//获取单元格范围A4：I_
var data = dataRange.getValues（）; //获取Range中每一行的值。
for（i in data）{
var row = data [i];
var emailAddress = row [1]; //第二列
var message = row [8]; //第九列
MailApp.sendEmail（emailAddress，subject，message）;
您好库珀
感谢您的信息，但即时通讯只是从脚本开始，我想从现在开始的基础知识开始，在那里生病。我无法想象你是在说我知道它的简单信息，但是我现在还不能解决它。
我是否删除这行 var sheet = SpreadsheetApp.getActiveSheet（）; 并替换为。我在 var range 行中遇到错误。我需要全面看到它，试图理解我在看什么。对不起，基本问题
var sheets = ['Sheet1'，'Sheet2'，'Sheet3'，'Sheet4']
for（var i = 0; i< sheets.length; i ++）
function sendEmails（）{
var sheet = SpreadsheetApp.getActiveSheet（ ）;
var range = sheet.getRange（1，2）; //获取单元格范围B1：B1
var subject = range.getValues（）; //从上面的范围获取主题行的值
var range = sheet.getRange（1，9）; //获取单元格的范围I1：I1
var numRows = range.getValues（）; //获取超出范围的电子邮件数量值
var startRow = 4; //第一行数据处理
var dataRange = sheet.getRange（startRow，1，numRows，9）//获取单元格范围A4：I_
var data = dataRange.getValues（）; //获取Range中每一行的值。
for（i in data）{
var row = data [i];
var emailAddress = row [1]; //第二列
var message = row [8]; //第九列
MailApp.sendEmail（emailAddress，subject，message）;
解决方案
完整的电子邮件发送和存档示例
这是我最近刚刚完成的一个完整示例。我认为是时候了解一些关于从电子表格发送电子邮件的时候了，因为我们遇到了这样的问题。现在在你的情况下，你想为不同的表格做一些事情，所以我给sendingMyEmails（）函数添加了一个参数。所以现在它发送邮件（sheetname）。您必须为每个工作表添加单独的函数调用，如下所示：
函数sendEmailsSheet1（）
{
发送邮件（'Sheet1'）;
}
或者你可以这样做：
函数sendAllSheets（）
{
var sheets = ['Sheet1'，'Sheet2'，'Sheet3'，'Sheet4']
for（var i = 0; i< sheets.length; i ++）
{
sendMyEmails（sheets [i]）;
$ / code $ / pre
无论哪种方式，只需要一个函数来发送所有的每张表中的电子邮件。此示例还包括将电子邮件存档到emailSent选项卡的功能。我收集所有信息，然后将其显示为HTML对话框，该对话框允许您检查您希望存档哪些电子邮件。
SendingEmails.gs
函数sendingMyEmails（sheetname）
{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sht = ss.getSheetByName（sheetname）;
var rng = sht.getDataRange（）;
var rngA = rng.getValues（）;
var s ='< html>< head>< script src =// ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js\"> ;< /脚本>< /头><身体GT;';
var someNotSent = false;
for（var i = 2; i
var dataA = {}; （var j = 0; j< rngA [1] .length; j ++）
{
dataA [rngA [1] [j]] = rngA [i] [j] （dataA.EntryDate&& dataA.Name&& dataA.Email&& dataA.Subject& amp;& amp; dataA.Body& amp;& dataA.SendIfYes =
='Yes'&& dataA.DateSent ==''）
{
MailApp.sendEmail（dataA.Email，dataA.Subject，dataA.Body，{replyTo：dataA.Sender}）;
sht.getRange（i + 1,8）.setValue（Utilities.formatDate（new Date（），'GMT-6'，M / dd / yyyy'\\\
'HH：mm：ss） ）;
SpreadsheetApp.flush（）;
}
else
{
var row = Number（i + 1）;
s + ='< div id =row'+ row +'>< input type =checkboxname =emailvalue ='+ Number（i + 1）+'/ >'+'电子邮件未发送：< strong>行：< / strong> '+号码（i + 1）+'< strong>名称：< / strong> '+ dataA.Name +'< strong>电子邮件地址：< / strong> '+ dataA.Email +'< strong>主题：< / strong> '+ dataA.Subject +'< strong> EntryDate：< / strong> '+ dataA.EntryDate +'< / div>';
someNotSent = true;
if（someNotSent）
{
s + ='< br />< input type =buttonvalue =ExitonClick = google.script.host.close（）; />< input type =buttonvalue =Archive CheckedonClick =getCheckedBoxes（\'email\'）; />;
s + ='< / body>< / html>';
var html = HtmlService.createHtmlOutputFromFile（'htmlToBody'）。setWidth（800）.setHeight（250）;
html.append（s）;
SpreadsheetApp.getUi（）。showModelessDialog（html，'Emails Not Sent'）;
函数archiveSelectedEmails（）
{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sht = ss.getSheetByName（'EmailSetup'）;
var rng = sht.getDataRange（）;
var rngA = rng.getValues（）;
var s ='< html>< head>< script src =// ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js\"> ;< /脚本>< /头><身体GT;';
var s ='';
for（var i = 2; i
var dataA = {}; （var j = 0; j< rngA [1] .length; j ++）
{
dataA [rngA [1] [j]] = rngA [i] [j]
}
var row = Number（i + 1）;
s + ='< div id =row'+ row +'>< input type =checkboxname =emailvalue ='+ Number（i + 1）+'/ >'+'< strong>行：< / strong> '+号码（i + 1）+'< strong>名称：< / strong> '+ dataA.Name +'< strong>电子邮件地址：< / strong> '+ dataA.Email +'< strong>主题：< / strong> '+ dataA.Subject +'< strong> DateSent：< / strong> '+ Utilities.formatDate（new Date（dataA.DateSent），'GMT-6'，'M / dd / yyyy HH：mm：ss'）+'< / div>';
}
s + ='< br />< input type =buttonvalue =ExitonClick =google.script.host.close（）; />< input type =buttonvalue =Archive CheckedonClick =getCheckedBoxes（\'email\'）; />;
var html = HtmlService.createHtmlOutputFromFile（'htmlToBody'）。setWidth（800）.setHeight（250）;
html.append（s）;
SpreadsheetApp.getUi（）。showModelessDialog（html，'选择电子邮件存档'）;
}
函数test（）
{
var rows = [3];
archiveSelectedRows（rows）;
函数archiveSelectedRows（行，sheetname）
{
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var sht = ss.getSheetByName（sheetname）;
var dest = ss.getSheetByName（'EmailsSent'）;
var rng = sht.getDataRange（）;
var rngA = rng.getValues（）;
var deleted = [];
for（var i = rngA.length-1; i> 1; i--）
{
if（rows.indexOf（i + 1）> -1）
{
deleted.push（Number（i + 1））;
dest.appendRow（rngA [i]）;
sht.deleteRow（i + 1）;
}
}
var msg ='行删除='+已删除;
var title ='行删除';
var timeout = 10;
回报已删除;
}
样本表
这是我的emailSetup表单的标题行图像，而emailsSent表单具有相同的列。我不使用对话框来标识已发送的电子邮件，以便我可以将它们归档到另一个表单上。
htmlToBody.html
<！DOCTYPE html>
< html>
< head>
< script src =// ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js\"> ;</script>
< script>
函数getCheckedBoxes（chkboxName）{
var checkboxes = document.getElementsByName（chkboxName）;
var rowsToArchive = [];
for（var i = 0; i< checkboxes.length; i ++）
{
if（checkboxes [i] .checked）
{
rowsToArchive.push（数（复选框[I]。价值））;
}
}
google.script.run
.withSuccessHandler（setResponse）
.archiveSelectedRows（rowsToArchive）;
}
函数setResponse（a）
{
var s ='< br />行：';
for（var i = 0; i< a.length; i ++）
{
if（i> 0）
{
s + ='，';
}
s + = a [i];
var id ='＃row'+ a [i]
$（id）.css（'display'，'none'）;
}
s + ='< br />总计：'+ a.length;
google.script.run.displayMessage（s，'Archived Rows'）
}
console.log（'script here'）;
< / script>
< / head>
< body>
Utility.gs
函数displayToast（msg，title，timeoutSeconds）
{
SpreadsheetApp.getActiveSpreadsheet（）。toast（msg，title，timeoutSeconds）
}
函数displayMessage（msg，title）
{
msg + ='< br />< input type =buttonvalue =ExitonClick =google.script.host.close ）; />;
var html = HtmlService.createHtmlOutput（msg）.setWidth（400）.setHeight（300）;
SpreadsheetApp.getUi（）。showModelessDialog（html，title）;
$ / code $ / $ p
Code.gs
< pre $函数onOpen（）
{
SpreadsheetApp.getUi（）。createMenu（'My Tools'）
.addItem（'Send Emails'，''发送邮件'）
.addItem（'存档选定电子邮件'，'archiveSelectedEmails'）
.addToUi（）;
}
I have found a few threads with how to update the script but I cant get it to work with the limit knowledge I have with Scripts
I have upto about 6 Tabs ,Sheet1,Sheet2,Sheet3,Sheet4,Sheet5,Sheet6 that I want to send emails up of but at this stage it only looks at the first sheet. Can someone let me know how to update this please
Thanks Jarrad
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 2); // Fetch the range of cells B1:B1
var subject = range.getValues(); // Fetch value for subject line from above range
var range = sheet.getRange(1, 9); // Fetch the range of cells I1:I1
var numRows = range.getValues(); // Fetch value for number of emails from above range
var startRow = 4; // First row of data to process
var dataRange = sheet.getRange(startRow, 1, numRows,9 ) // Fetch the range of cells A4:I_
var data = dataRange.getValues(); // Fetch values for each row in the Range.
for (i in data) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[8]; // Ninth column
MailApp.sendEmail(emailAddress, subject, message);
}
}
Hi Cooper
thanks for the info but im only just starting out with scripts I want to start with the basics for now and ill work out form there. I cant picture wan you are saying I know its simple info but I cant work it out just yet.
Do I remove this line var sheet = SpreadsheetApp.getActiveSheet(); and replace with. I get errors on line var range line. I need to see it in full to try and understand what Im looking at. Sorry for the basic question
var sheets=['Sheet1','Sheet2','Sheet3','Sheet4']
for(var i=0;i<sheets.length;i++)
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(1, 2); // Fetch the range of cells B1:B1
var subject = range.getValues(); // Fetch value for subject line from above range
var range = sheet.getRange(1, 9); // Fetch the range of cells I1:I1
var numRows = range.getValues(); // Fetch value for number of emails from above range
var startRow = 4; // First row of data to process
var dataRange = sheet.getRange(startRow, 1, numRows,9 ) // Fetch the range of cells A4:I_
var data = dataRange.getValues(); // Fetch values for each row in the Range.
for (i in data) {
var row = data[i];
var emailAddress = row[1]; // Second column
var message = row[8]; // Ninth column
MailApp.sendEmail(emailAddress, subject, message);
解决方案 Complete Email Sending and Archiving Example
Here's a complete example I just did recently. I thought it was time for me to learn a little about sending emails from a spreadsheet since we get questions like this a lot. Now in your case you want to do for different sheets so I added a parameter to my sendingMyEmails() function. So that now it's sendingMyEmails(sheetname). You have to add either separate function calls for each sheet like below:
function sendEmailsSheet1()
{
sendingMyEmails('Sheet1');
}
Or you could do something like this:
function sendAllSheets()
{
var sheets=['Sheet1','Sheet2','Sheet3','Sheet4']
for(var i=0;i<sheets.length;i++)
{
sendMyEmails(sheets[i]);
}
}
Either way you only need one function for sending all of the emails in each sheet. This example also includes the capability to archive your emails to an emailSent tab. I collect all that information and then display it as an HTML Dialog which allows you to check off which emails you wish to have archived.
SendingEmails.gs
function sendingMyEmails(sheetname)
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sht=ss.getSheetByName(sheetname);
var rng=sht.getDataRange();
var rngA=rng.getValues();
var s='<html><head><script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script></head><body>';
var someNotSent=false;
for(var i=2;i<rngA.length;i++)
{
var dataA={};
for(var j=0;j<rngA[1].length;j++)
{
dataA[rngA[1][j]]=rngA[i][j];
}
if(dataA.EntryDate && dataA.Name && dataA.Email && dataA.Subject && dataA.Body && dataA.SendIfYes=='Yes' && dataA.DateSent=='')
{
MailApp.sendEmail(dataA.Email, dataA.Subject, dataA.Body, {replyTo:dataA.Sender});
sht.getRange(i+1,8).setValue(Utilities.formatDate(new Date(), 'GMT-6', "M/dd/yyyy'\n'HH:mm:ss"));
SpreadsheetApp.flush();
}
else
{
var row=Number(i+1);
s+='<div id="row' + row + '"><input type="checkbox" name="email" value="' + Number(i+1) + '" />' + 'Email Not Sent: <strong>Row:</strong> ' + Number(i+1) + ' <strong>Name:</strong> ' + dataA.Name + ' <strong>Email:</strong> ' + dataA.Email + ' <strong>Subject:</strong> ' + dataA.Subject + ' <strong>EntryDate:</strong> ' + dataA.EntryDate + '</div>';
someNotSent=true;
}
}
if(someNotSent)
{
s+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" /><input type="button" value="Archive Checked" onClick="getCheckedBoxes(\'email\');" />';
s+='</body></html>';
var html=HtmlService.createHtmlOutputFromFile('htmlToBody').setWidth(800).setHeight(250);
html.append(s);
SpreadsheetApp.getUi().showModelessDialog(html, 'Emails Not Sent');
}
}
function archiveSelectedEmails()
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sht=ss.getSheetByName('EmailSetup');
var rng=sht.getDataRange();
var rngA=rng.getValues();
var s='<html><head><script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script></head><body>';
var s='';
for(var i=2;i<rngA.length;i++)
{
var dataA={};
for(var j=0;j<rngA[1].length;j++)
{
dataA[rngA[1][j]]=rngA[i][j];
}
var row=Number(i+1);
s+='<div id="row' + row + '"><input type="checkbox" name="email" value="' + Number(i+1) + '" />' + ' <strong>Row:</strong> ' + Number(i+1) + ' <strong>Name:</strong> ' + dataA.Name + ' <strong>Email:</strong> ' + dataA.Email + ' <strong>Subject:</strong> ' + dataA.Subject + ' <strong>DateSent:</strong> ' + Utilities.formatDate(new Date(dataA.DateSent), 'GMT-6', "M/dd/yyyy HH:mm:ss") + '</div>';
}
s+='<br /><input type="button" value="Exit" onClick="google.script.host.close();" /><input type="button" value="Archive Checked" onClick="getCheckedBoxes(\'email\');" />';
var html=HtmlService.createHtmlOutputFromFile('htmlToBody').setWidth(800).setHeight(250);
html.append(s);
SpreadsheetApp.getUi().showModelessDialog(html, 'Select Emails to Archive');
}
function test()
{
var rows=[3];
archiveSelectedRows(rows);
}
function archiveSelectedRows(rows,sheetname)
{
var ss=SpreadsheetApp.getActiveSpreadsheet();
var sht=ss.getSheetByName(sheetname);
var dest=ss.getSheetByName('EmailsSent');
var rng=sht.getDataRange();
var rngA=rng.getValues();
var deleted=[];
for(var i=rngA.length-1;i>1;i--)
{
if(rows.indexOf(i+1)>-1)
{
deleted.push(Number(i+1));
dest.appendRow(rngA[i]);
sht.deleteRow(i+1);
}
}
var msg='Rows Deleted = ' + deleted;
var title='Rows Deleted';
var timeout=10;
return deleted;
}
Sample Sheet
This is an image of the header rows for my emailSetup sheet and the emailsSent sheet has the same columns. I don't use a dialog to identify the emails that are already sent so that I can archive them away on another sheet.
htmlToBody.html
<!DOCTYPE html>
<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
<script>
function getCheckedBoxes(chkboxName) {
var checkboxes = document.getElementsByName(chkboxName);
var rowsToArchive = [];
for (var i=0; i<checkboxes.length; i++)
{
if (checkboxes[i].checked)
{
rowsToArchive.push(Number(checkboxes[i].value));
}
}
google.script.run
.withSuccessHandler(setResponse)
.archiveSelectedRows(rowsToArchive);
}
function setResponse(a)
{
var s='<br />Rows: ';
for(var i=0;i<a.length;i++)
{
if(i>0)
{
s+=', ';
}
s+=a[i];
var id='#row' + a[i]
$(id).css('display','none');
}
s+='<br />Total: ' + a.length;
google.script.run.displayMessage(s,'Archived Rows')
}
console.log('script here');
</script>
</head>
<body>
Utility.gs
function displayToast(msg,title,timeoutSeconds)
{
SpreadsheetApp.getActiveSpreadsheet().toast(msg, title, timeoutSeconds)
}
function displayMessage(msg,title)
{
msg+='<br /><input type="button" value="Exit" onClick="google.script.host.close()"; />';
var html=HtmlService.createHtmlOutput(msg).setWidth(400).setHeight(300);
SpreadsheetApp.getUi().showModelessDialog(html, title);
}
Code.gs
function onOpen()
{
SpreadsheetApp.getUi().createMenu('My Tools')
.addItem('Send Emails','sendingMyEmails')
.addItem('Archive Selected Emails','archiveSelectedEmails')
.addToUi();
}
这篇关于Google Spreadsheets更新带有多个标签的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
