I'm new using google spreadsheet. I'm trying to build device rental spreadsheet to track. When I change on the Borrower cell, then it will be generate to the changelog sheet.
I want to ask :
- What should I write on the "appendRow" below?
- When I paste "Boxed" text to the E1,E3,E4 cells in one action, is just add 1 row on the change log sheet. How to record all paste action on multiple cell (including random cell)?
The cell :
"No" "Phone" "Code" "Price" "Borrower"
1 Iphone-X 1234 25$ John
2 Iphone-8 1235 20$ Boxed
3 Iphone-7s 1236 15$ Lina
4 Iphone-6s 1237 10$ Michael
function onEdit(e) {
// This script records changes to the spreadsheet on a "Changelog" sheet.
// The changelog includes these columns:
// "Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"
// Users are logged by email address.
// Source 1: https://productforums.google.com/d/topic/docs/az365_ypIV0/discussion
// Source 2: https://productforums.google.com/forum/#!topic/docs/AI9OxbOtvWE
var sheet = e.source.getActiveSheet();
if (sheet.getName() !== 'All Device'|| e.range.getColumn() != 5)
{
return;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.toast("Running Script..", "Status");
var ssdevice = ss.getSheetByName('All Device');
var cell = SpreadsheetApp.getActiveRange();
var d = new Date(); var timeStamp = d.getTime();
var date = Utilities.formatDate(new Date(), "GMT+7", "dd MMMM yyyy - HH:mm:ss")
var endDate = date
var logdate = Utilities.formatDate(new Date(), "GMT+7", "- MMMM yyyy");
var logendDate = logdate
var changelogSheetName = "Log " + logendDate;
var currentSheet = ss.getActiveSheet();
var currentSheetName = ssdevice.getName();
var newValue = cell.getValue();
if (currentSheetName == changelogSheetName) return;
var changelogSheet = ss.getSheetByName(changelogSheetName);
if (changelogSheet == null) {
changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
Utilities.sleep(2000); // give time for the new sheet to render before going back
ss.setActiveSheet(currentSheet);
changelogSheet.getRange('A1:F1').setBackground('#E0E0E0');
changelogSheet.appendRow(["No", "Device", "Code", "Price", "Status", "Timestamp"]);
changelogSheet.deleteColumns(8,19);
changelogSheet.setFrozenRows(1);
changelogSheet.setColumnWidth(1, 30);
changelogSheet.setColumnWidth(6, 170);
changelogSheet.setColumnWidth(7, 170);
changelogSheet.protect();
}
changelogSheet.appendRow([ changelogSheet.getLastRow(), ..... , ..... , .... , ..... , endDate ]);
ss.toast("Done. Task has completed.", "Status", 1);
}
Aucun commentaire:
Enregistrer un commentaire