[GOOGLE-SHEETS] thực hiện chuyển dữ liệu sang bản khác (ok)
Last updated
Last updated
Một ví dụ đã hoàn thành
var DOC_ID = '1WyrymiNZh9KSFvEQXAEXencqwFqrgx5_qp1574xKovk';
var SHEET_NAME = '売上データDEMO';
function getData() {
// get our source data
var doc = SpreadsheetApp.openById(DOC_ID);
var sheet = doc.getSheetByName(SHEET_NAME);
var numRows = sheet.getLastRow() - 1;
if (numRows > 20) { //optional to limit rows returned
numRows = 20;
}
var data = sheet.getRange(2, 1, numRows, sheet.getLastColumn()).getValues();
// get sheet headers to build object array
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// simple replace space with underscore
headers = headers.map(function(p) {
return p.toString().replace(/\s+/g, '_');
});
var result = [];
// loop through the rows and build object arrary
// looping in reverse to get newest data first
for (var r = data.length - 1; r >= 0; r--) {
var row = data[r];
var record = {};
for (var h in headers) {
var type = typeof row[h];
// for this tutorial if the column name is Email obscure the result
if (headers[h] == "Email") {
row[h] = obscureEmail_(row[h]);
}
// Execution API can only return basic types (strings, arrays, objects, numbers and booleans)
// As we are delaing with Google Sheet data we only need to detect dates
if (row[h] instanceof Date) {;
row[h] = String(row[h]);
}
record[headers[h]] = row[h];
}
result.push(record);
}
return { "comments": result };
}
// ===
var parameters = {Date: "aaaaaaa",header_row: 1};
function sendQuote(parameters = {Date: "aaaaaaa",header_row: 1}) {
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(DOC_ID);
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row the parameters
var headRow = parameters.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow() + 1; // get next row
console.log(headers);
var row = [];
// loop through the header columns
for (i in headers) {
if (headers[i] == "Timestamp") { // special case if you include a 'Timestamp' column
row.push(new Date());
} else if (headers[i] == "Date") { // special case if you include a 'email' column
row.push(Session.getEffectiveUser().getEmail());
} else { // else use header name to get data
row.push(parameters[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return all the from the sheet data
console.log(parameters);
return getData();
} catch (e) {
// if error return this
console.log(e);
console.log(parameters);
return { "result": JSON.stringify(e) };
} finally { //release lock
lock.releaseLock();
}
}
