下面是使用for回圈洗掉除“G”和“M”列之外的整個列的代碼，但是這個程序太慢了，有沒有辦法更快地執行它？
var lastCol = newSheet.getLastColumn();
var keep = [7,13];
for (var col=lastCol; col > 0; col--) {
if (keep.indexOf(col) == -1) {
newSheet.deleteColumn(col);
}
}
uj5u.com熱心網友回復：
以下是使用 SpreadsheetApp API 執行此操作的方法：
function test() {
const keep = [7, 13]; // columns G and M
const newSheet = SpreadsheetApp.getActiveSheet();
deleteColumns_(newSheet, keep);
}
/**
* Deletes all columns in sheet except the ones whose column numbers
* are listed in columnsToKeep.
*
* The columnsToKeep array [1, 2, 7, 13] means that columns A, B, G and M
* will remain while other columns are deleted.
*
* @param {Sheet} sheet The sheet where to delete columns.
* @param {Number[]} columnsToKeep Array of column numbers to keep.
*/
function deleteColumns_(sheet, columnsToKeep) {
// version 1.0, written by --Hyde, 13 June 2022
// - see https://stackoverflow.com/q/72600890/13045193
const columnsToDelete = [];
for (let i = 1, maxColumns = sheet.getMaxColumns(); i <= maxColumns; i ) {
if (!columnsToKeep.some(columnNumber => i === columnNumber)) {
columnsToDelete.push(i);
}
}
const tuples = getRunLengths_(columnsToDelete).reverse();
tuples.forEach(([columnStart, numColumns]) => sheet.deleteColumns(columnStart, numColumns));
}
/**
* Counts consecutive numbers in an array and returns a 2D array that
* lists the first number of each run and the number of items in each run.
*
* The numbers array [1, 2, 3, 5, 8, 9, 11, 12, 13, 5, 4] will get
* the result [[1, 3], [5, 1], [8, 2], [11, 3], [5, 1], [4, 1]].
*
* For best results, sort the numbers array like this:
* const runLengths = getRunLengths_(numbers.sort((a, b) => a - b));
* Note that duplicate values in numbers will give duplicates in result.
*
* @param {Number[]} numbers The numbers to group into runs.
* @return {Number[][]} The numbers grouped into runs, or [] if the array is empty.
*/
function getRunLengths_(numbers) {
// version 1.1, written by --Hyde, 31 May 2021
if (!numbers.length) {
return [];
}
return numbers.reduce((accumulator, value, index) => {
if (!index || value !== 1 numbers[index - 1]) {
accumulator.push([value]);
}
const lastIndex = accumulator.length - 1;
accumulator[lastIndex][1] = (accumulator[lastIndex][1] || 0) 1;
return accumulator;
}, []);
}
uj5u.com熱心網友回復：
我相信你的目標如下。
- 您想降低腳本的處理成本。
- 您想洗掉除“G”和“M”列之外的所有列。
在這種情況下，使用 Sheets API 怎么樣？使用 Sheets API 時，您的腳本如下所示。我認為當使用 Sheets API 時，處理成本可能會降低一點。
修改后的腳本：
在使用此腳本之前，請在 Advanced Google services 中啟用 Sheets API。
function myFunction() {
var sheetName = "Sheet1"; // Please set the sheet name.
var keep = [7, 13];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = ss.getSheetByName(sheetName);
var lastCol = newSheet.getLastColumn(); // or newSheet.getMaxColumns()
var sheetId = newSheet.getSheetId();
var requests = [...Array(lastCol)].reduce((ar, _, i) => {
if (!keep.includes(i 1)) {
ar.push({ deleteDimension: { range: { sheetId, startIndex: i, endIndex: i 1, dimension: "COLUMNS" } } });
}
return ar;
}, []).reverse();
if (requests.length == 0) return;
Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}
- 運行此腳本時，除“G”和“M”列之外的所有列都將被洗掉。
- 從您的腳本中，
var lastCol = newSheet.getLastColumn();使用。在這種情況下，使用資料范圍。如果要檢查所有列，請使用var lastCol = newSheet.getMaxColumns();而不是它。
參考：
- 方法：電子表格.batchUpdate
- 洗掉維度請求
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/489733.html