/**
*
*/
function runSheet() {
var spec = ['Мой супер лист'];
var spreadsheet = SpreadsheetApp.openById(
'ABCD1234'
).copy('tmp');
spec.forEach(function(sheetName) {
var dr = spreadsheet.getSheetByName(sheetName).getDataRange();
dr.setValues(dr.getValues());
});
spreadsheet.getSheets().forEach(function(sheet) {
if (spec.indexOf(sheet.getName()) < 0) spreadsheet.deleteSheet(sheet);
});
var spreadsheetId = spreadsheet.getId();
var file = exportSpreadsheetToFile_(spreadsheetId, 'xlsx');
DriveApp.getFileById(spreadsheetId).setTrashed(true);
return file;
}
exportSpreadsheetToFile_
можно найти тут.MailApp.sendEmail({
to: "recipient@example.com",
subject: "Logos",
attachments: [runSheet().getBlob()]
});
/**
* @param {GoogleAppsScript.Events.SheetsOnEdit} e
*/
function onEdit(e) {
if (
e.range.getSheet().getName() === 'Sheet1' &&
e.range.getA1Notation() === 'M2'
)
CopyList();
}
/**
*
*/
function CopyList() {
var sss = SpreadsheetApp.openById('ID-ТАБЛИЦЫ1');
var ss = sss.getSheetByName('Sheet1');
var from = ss;
var fromValues = from.getDataRange().getValues();
var fromData = fromValues;
var tss = SpreadsheetApp.openById('ID-ТАБЛИЦЫ2');
var ts = tss.getSheetByName('Sheet2');
ts.getRange(
ts.getLastRow() + 1,
1,
fromData.length,
fromData[0].length
).setValues(fromData);
}
onEdit
из другой функции или из редактора.TypeError: Cannot read property 'range' of undefined (строка 3, файл se)
se
в 3й строке
есть некоторая переменная со значением undefined
, свойство range
которой прочитать невозможно. Тут все очевидно - у undefined
нет свойств.e
. И мы ее получаем в системную функцию onEdit
. Это означает, что система сама передает контекст в эту функцию./**
* Тестирование триггера для события EDIT
*/
function runOnEdit() {
var source = SpreadsheetApp.getActive();
var range = source.getRangeByName('Sheet!!B26');
/**
* @type {GoogleAppsScript.Events.SheetsOnEdit}
*/
var e = {
authMode: ScriptApp.AuthMode.LIMITED,
oldValue: undefined, // ну или что хотите
range: range,
value: range.getValue(),
source: source,
triggerUid: 0,
user: Session.getActiveUser(),
};
onEdit(e);
}
/**
*
* @param {GoogleAppsScript.Events.SheetsOnEdit} e
*/
function onEdit(e) {
// Работает простой триггер
}
/**
* User action. Runs the snippet
*/
function run2() {
const sheet = SpreadsheetApp.getActiveSheet();
const base = sheet.getRange('J3:J');
const colFormula = sheet.getRange('J3');
draggDownOneFormula_(base, colFormula);
}
/**
* @param {GoogleAppsScript.Spreadsheet.Range} base
* @param {GoogleAppsScript.Spreadsheet.Range} colFormula
*/
function draggDownOneFormula_(base, colFormula) {
const baseValues = base.getValues();
const lastBase =
baseValues.length - baseValues.reverse().findIndex(row => row[0] !== '');
const colFormulaFormula = colFormula.getFormula();
colFormula
.getSheet()
.getRange(base.getRow(), colFormula.getColumn(), lastBase + 1)
.setFormula(colFormulaFormula);
}
/**
* @file Dragg down a formula
* @url https://qna.habr.com/q/709715
* */
/**
* User action. Runs the snippet
*/
function run() {
const sheet = SpreadsheetApp.getActiveSheet();
const base = sheet.getRange('C3:C');
const colFormula = sheet.getRange('J3');
draggDownFormulas_(base, colFormula);
}
/**
* @param {GoogleAppsScript.Spreadsheet.Range} base
* @param {GoogleAppsScript.Spreadsheet.Range} colFormula
*/
function draggDownFormulas_(base, colFormula) {
const baseValues = base.getValues();
const lastBase =
baseValues.length - baseValues.reverse().findIndex(row => row[0] !== '');
const colFormulaFormula = colFormula.getFormula();
colFormula
.getSheet()
.getRange(base.getRow(), colFormula.getColumn(), lastBase)
.setFormula(colFormulaFormula);
}
C
заполнена на одну строку ниже, чем массив данныхJ3
function copyTabList() {
var from = SpreadsheetApp.getActiveSheet();
var fromValues = from.getDataRange().getValues();
var fromData = fromValues.slice(1);
var tss = SpreadsheetApp.openById('Id');
var ts = tss.getSheetByName('Sheet3');
ts.getDataRange()
.offset(1, 0)
.clearContent()
.getRange(2, 1, fromData.length, fromData[0].length)
.setValues(fromData);
}
function copyTabList() {
var from = SpreadsheetApp.getActiveSheet();
var fromValues = from
.getDataRange()
.offset(1, 0)
.getValues();
var fromData = fromValues;
var tss = SpreadsheetApp.openById('Id');
var ts = tss.getSheetByName('Sheet3');
ts.getDataRange()
.offset(1, 0)
.clearContent()
.getSheet()
.getRange(2, 1, fromData.length, fromData[0].length)
.setValues(fromData);
}
userActionsCopyToLogs
уже у себя./**
*
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Custom menu')
.addItem('Copy to logs', 'userActionsCopyToLogs')
.addToUi();
}
/**
*
*/
function userActionsCopyToLogs() {
var headers = ['Name', 'Number'];
var from = SpreadsheetApp.getActiveSheet();
if (from.getName() !== 'Sheet1') {
SpreadsheetApp.getActive().toast('Activate a range on "Sheet1"');
return;
}
var fromValues = from.getDataRange().getValues();
var fromHeaders = fromValues[0].map(function(h) {
return headers.indexOf(h);
});
var activeRange = SpreadsheetApp.getActiveRange();
var rowStart = activeRange.getRow();
var rowEnd = activeRange.getLastRow();
var fromData = fromValues
.filter(function(row, i) {
return i >= rowStart - 1 && i <= rowEnd - 1;
})
.map(function(row) {
return row.filter(function(_, j) {
return fromHeaders[j] > -1;
});
});
var to =
SpreadsheetApp.getActive().getSheetByName('Logs') ||
SpreadsheetApp.getActive().insertSheet('Logs');
to.getRange(to.getLastRow() + 1, 1, fromData.length, fromData[0].length)
.setValues(fromData)
.activate();
}
var headers = ['Name', 'Number'];
так, чтобы копировались данные из нужных вам колонок.function onEdit() {
run2();
}
function run2() {
/* Remove dash */
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() === 'Журнал вода данных') return;
var archive = SpreadsheetApp.getActive().getSheetByName('Журнал вода данных');
var action = function(values, i, i2) {
var data = values.slice(i, i + i2);
archive
.getRange(archive.getLastRow() + 1, 1, data.length, data[0].length)
.setValues(data);
};
var condition = function(values, i) {
var row = values[i];
return (
i > 0 && row[0] !== '' && row[1] !== '' && row[3] !== '' && row[5] !== ''
);
};
deleteRowsByConditional_(sheet, condition, action);
}
function deleteRowsByConditional_(sheet, condition, action) {
sheet
.getDataRange()
.getValues()
.forEach(
function(_, i, arr) {
var j = arr.length - i - 1;
if (this.condition.apply(null, [arr, j])) {
this.isContinue++;
if (j > 0) return;
}
if (this.isContinue > 0) {
var prevPos = j + 1; // It's reversed
if (action) action(arr, prevPos, this.isContinue);
this.sheet.deleteRows(prevPos + 1, this.isContinue);
this.isContinue = 0;
return;
}
return;
},
{ sheet: sheet, condition: condition, isContinue: 0 }
);
}
run2()
к меню.doPost(e)
передается запрос от внешнего источника только тогда, когда ваш сервис опубликован и отправлен запрос POST.doPost
из редактора кода. MailApp.sendEmail({
...
body: 'https://drive...',
...
});
var file = DriveApp.getFilesByName('test123.pdf');
if (file.hasNext()) {
MailApp.sendEmail(emailAddress, subject, message, {
attachments: [file.next().getAs(MimeType.PDF)],
name: 'Automatic Emailer Script'
}
function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var activeRange = sheet.getActiveRange();
var data = sheet
.getRange(
activeRange.getRow(),
1,
activeRange.getLastRow() - activeRange.getRow() + 1,
6
)
.getValues();
data.forEach(function(row) {
var file = DriveApp.getFilesByName(row[5]);
if (file.hasNext()) {
var namesender = row[0];
var emailAddress = row[1];
var hiddencopy = row[2];
var subject = row[3];
var message = row[5];
var information = row[4];
var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
Logger.log('Remaining email quota: ' + emailQuotaRemaining);
MailApp.sendEmail({
to: emailAddress,
bcc: hiddencopy,
replyTo: 'supportmp@test.ru',
name: namesender,
subject: subject + ' ' + information,
htmlBody: message + ' ' + https,
attachments: [file.next().getAs(MimeType.PDF)],
});
} else {
Logger.log('Файл для отправки не найден');
}
});
}