how to remove empty rows and columns using batch request

Question

I have a code by which I remove all empty rows and columns from a sheet. Is it possible to use a batch request to speed up the script's work, because when there are a lot of files, it takes a very long time?

Code below

    while ( files.hasNext()){
   var file1 = files.next().getId();
 var sheet = SpreadsheetApp.openById(file1);
var allsheets = sheet.getSheets();
for (var s in allsheets){
var sheet=allsheets[s]
var maxColumns = sheet.getMaxColumns(); 
var lastColumn = sheet.getLastColumn();
var maxRows = sheet.getMaxRows(); 
var lastRow = sheet.getLastRow();
if (maxRows-lastRow === 0){}
else {
if (maxRows-lastRow != 0){
      sheet.deleteRows(lastRow+1, maxRows-lastRow);
      }
      console.log(sheet)
if (maxColumns-lastColumn != 0){
      sheet.deleteColumns(lastColumn+1, maxColumns-lastColumn);
      }
      console.log(sheet)
    }
  }
 }

I would be grateful for any help

Answer 1

I believe your goal as follows.

• You want to reduce the process cost of your script.
• You want to use the batch request.

Modification points:

• In this case, as a simple method, I would like to propose to use the batchUpdate method of Sheets API for deleting the rows and columns. I thought that by this, the process cost will be reduced a little.

When above points are reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API at Advanced Google services.

while (files.hasNext()){
  var file1 = files.next().getId();
  var ss = SpreadsheetApp.openById(file1);
  var sheets = ss.getSheets();
  var requests = sheets.reduce((ar, sheet) => {
    var sheetId = sheet.getSheetId();
    var maxColumns = sheet.getMaxColumns(); 
    var lastColumn = sheet.getLastColumn();
    var maxRows = sheet.getMaxRows(); 
    var lastRow = sheet.getLastRow();
    if (lastRow > 1 && maxRows > lastRow) {
      ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "ROWS", startIndex: lastRow}}});
    }
    if (lastColumn > 1 && maxColumns > lastColumn) {
      ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "COLUMNS", startIndex: lastColumn}}});
    }
    
    // For empty sheet, when you want to leave only cell "A1", please use the following script.
    // if (lastRow == 0 && lastColumn == 0 && maxRows > 1 && maxColumns > 1) {
    //   ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "ROWS", startIndex: 1}}});
    //   ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "COLUMNS", startIndex: 1}}});
    // }

    return ar;
  }, []);
  if (requests.length > 0) {
    Sheets.Spreadsheets.batchUpdate({requests: requests}, id);
  }
}

• When this script is run, the empty rows and columns are deleted in all sheets in the Spreadsheet.
• For empty sheet, when you want to leave only cell "A1" in the sheet, please use the commented script.

Note:

• I think that above sample script might be suitable when the number of files is small. But, when the number of files are large, when the file list is retrieved using Drive API, the process cost might be able to be reduced more. In this case, the process cost for retrieving the file list using Drive API v3 is lower than that using Drive API v2 of Advanced Google services. So this sample script used Drive API v3 using UrlFetchApp. In this sample script, from your script, it supposes that the files are existing in just under a folder. Please be careful this.

• When you use this script, please enable Drive API at Advanced Google services.

    function myFunction() {
      var topFolderId = "###";  // Please set the folder ID of the top folder.
  
      // Retrieve file list using Drive API v3.
      const headers = {authorization: `Bearer ${ScriptApp.getOAuthToken()}`};
      const q = `'${topFolderId}' in parents and mimeType='${MimeType.GOOGLE_SHEETS}' and trashed=false`;
      const url = `https://www.googleapis.com/drive/v3/files?pageSize=1000&q=${q}&fields=${encodeURIComponent("nextPageToken,files(id)")}`;
      let pageToken = "";
      let files = [];
      do {
        const res = UrlFetchApp.fetch(url + "&pageToken=" + pageToken, {headers: headers, muteHttpExceptions: true});
        if (res.getResponseCode() != 200) throw new Error(res.getContentText());
        const obj = JSON.parse(res.getContentText());
        files = files.concat(obj.files);
        pageToken = obj.nextPageToken || "";
      } while(pageToken);
  
      // Remove empty rows and columns using Sheets API.
      files.forEach(({id}) => {
        var ss = SpreadsheetApp.openById(id);
        var sheets = ss.getSheets();
        var requests = sheets.reduce((ar, sheet) => {
          var sheetId = sheet.getSheetId();
          var maxColumns = sheet.getMaxColumns(); 
          var lastColumn = sheet.getLastColumn();
          var maxRows = sheet.getMaxRows(); 
          var lastRow = sheet.getLastRow();
          if (lastRow > 1 && maxRows > lastRow) {
            ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "ROWS", startIndex: lastRow}}});
          }
          if (lastColumn > 1 && maxColumns > lastColumn) {
            ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "COLUMNS", startIndex: lastColumn}}});
          }
          if (lastRow == 0 && lastColumn == 0 && maxRows > 1 && maxColumns > 1) {
            ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "ROWS", startIndex: 1}}});
            ar.push({deleteDimension: {range: {sheetId: sheetId, dimension: "COLUMNS", startIndex: 1}}});
          }
          return ar;
        }, []);
        if (requests.length > 0) {
          Sheets.Spreadsheets.batchUpdate({requests: requests}, id);
        }
      });
    }
  

References:

• Method: spreadsheets.batchUpdate
• DeleteDimensionRequest
• reduce()