javascript - 范围高度不正确 - Google Apps 脚本

Question

我目前正在尝试从另一个电子表格中获取值，然后将其粘贴到目标电子表格中。我遇到的问题是，当我运行此代码时，我得到的范围高度和范围宽度不正确。我读过一些有关二维数组的内容，但我相信这里已经有一个二维数组可以粘贴到电子表格中。感谢您抽出时间。

function GmailToDrive_StaticTest(gmailSubject, importFileID){


 var threads = GmailApp.search('subject:' + gmailSubject + ' -label:uploaded has:attachment'); // performs Gmail query for email threads

 for (var i in threads){
      var messages = threads[i].getMessages(); // finds all messages of threads returned by the query

      for (var j in messages){
           var attachments = messages[j].getAttachments(); // finds all attachments of found messages
           var timestamp = messages[j].getDate(); // receives timestamp of each found message
           var timestampMinusOne = new Date(timestamp.getTime() - (86400000)); // sets the received timestamp to exactly one day prior (# in milliseconds)
           var date = Utilities.formatDate(timestampMinusOne, "MST", "yyyy-MM-dd"); // cleans the timestamp string

           for (var k in attachments){
                var blobs = {
                      dataType: attachments[k].getContentType(), // retrives the file types of the attachments
                      data: attachments[k].copyBlob(), // creates blob files for every attachment
                      fileName: attachments[k].getName()
                      };

                var tempFile = DriveApp.createFile(blobs.data.setContentType('text/csv')).setName(blobs.fileName.split("-", 1).toString() + date); // creates csv files in drive's root per blob file

                var tempFileConverted = Drive.Files.copy( {}, tempFile.getId(), {convert: true} ); // converts created files to gsheets
                var importData = {
                      file: tempFileConverted,
                      ID: tempFileConverted.getId(),
                      Sheet1: SpreadsheetApp.openById(tempFileConverted.getId() ).getActiveSheet(),
                      Sheet1_Values: SpreadsheetApp.openById(tempFileConverted.getId() ).getActiveSheet().getDataRange().getValues()
                      };

                tempFile.setTrashed(true);

                var importData_Sheet1_Rows = importData.Sheet1.getMaxRows(); - 2;
                var importData_Sheet1_Columns = importData.Sheet1.getMaxColumns(); - 2;
                var destSheet = SpreadsheetApp.openById(importFileID).getSheets()[0]; 

                destSheet.clearContents();
                Logger.log(importData.Sheet1_Values)
                destSheet.getRange(1, 1, importData_Sheet1_Rows, importData_Sheet1_Columns).setValues(importData.Sheet1_Values);
                DriveApp.getFileById(importData.ID).setTrashed(true);    


           }
      }
 }

}

Answer 1

getMaxRows() 和 getMaxColumns() 返回工作表中的最大列数和行数，而 getDataRange().getValues() > 返回工作表中包含数据的所有值。

因此，除非工作表中的所有单元格都有数据，否则尺寸将不匹配!

您能做的最好的事情就是获取数据数组的实际大小，并使用它来设置目标工作表中的值的范围。

它(更)简单地像这样:

destSheet.getRange(1, 1, importData.Sheet1_Values.length, importData.Sheet1_Values[0].length).setValues(importData.Sheet1_Values);

您不需要行和列的其他值，只需在脚本中忽略它即可。