How to auto append new column everyday to a google sheet via google script and add data via gmail attachment?

Question

I am getting an email every day with 3 attachments (xlsx). I want the last column of the second tab of the 2nd attachment to be appended to an existing google sheet (in the column next to the last one). I have done some research and came up with a code to export the xlsx file to google drive, then convert it to CSV. I am able to update the whole google sheet with the data in the second attachment, but I am not able to copy the last column of the sheet and append it to an existing sheet. The destination sheet has 4 columns as of now, and I want a new column to be added every day along with the latest data from the attachment in my Gmail. Below is the code which I am using:

function importCSVFromGmail() {

  var thread = GmailApp.search("Scheduled Reports from RingCentral"); // enter search criteria here
  var messages = thread[0].getMessages();
  var len = messages.length;
  var message=messages[len-1] //get last message
  var attachments = message.getAttachments(); // Get attachment of first message
  var xlsxBlob = attachments[1]; // Is supposes that attachments[1] is the blob of xlsx file. I get 3 attachments in gmail, and I am using the 2nd one, so using attachment[1]
  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[2]; // There is the data in 1st tab.
  var data = sheet.getDataRange().getValues();
  var sheet2 = SpreadsheetApp.openByUrl("link to the existing google sheet").getSheetByName("Test"); //add the URL of the google sheet along with the tab name, in this case, "Test"
  sheet2.clearContents();
  var copyColumn = sheet.getRange("D1:D21"); //copying the column which I need to paste
  var range = sheet2.getRange(5, 5, data.length, data[0].length); //5 is the column where I was testing to paste the data 
  var lastColumn = sheet2.getLastColumn();
  copyColumn.copyValuesToRange(sheet2, lastColumn+1, lastColumn+1, 1, 23); // there are 23 rows
  Drive.Files.remove(convertedSpreadsheetId); // Remove the converted file.
}

I am pretty sure I am doing some blunder in this code, also I am getting this exception and the code is not working:

Exception: Target sheet and source range must be on the same spreadsheet. (line 23, file "AP Script")

Please let me know if my requirements are not clear.

Answer 1

try this:

function importCSVFromGmail() {
  var thread = GmailApp.search("Scheduled Reports from RingCentral"); // enter search criteria here
  var messages = thread[0].getMessages();
  var len = messages.length;
  var message=messages[len-1] //get last message
  var attachments = message.getAttachments(); // Get attachment of first message
  var xlsxBlob = attachments[1]; // Is supposes that attachments[1] is the blob of xlsx file. I get 3 attachments in gmail, and I am using the 2nd one, so using attachment[1]
  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id;
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[2]; // There is the data in 1st tab.
  var data = sheet.getDataRange().getValues();
  var sheet2 = SpreadsheetApp.openByUrl("link to the existing google sheet").getSheetByName("Test"); //add the URL of the google sheet along with the tab name, in this case, "Test"
  sheet2.clearContents();
  var vs = sheet.getRange("D1:D21").getValues(); 
  var range = sheet2.getRange(5, 5, data.length, data[0].length); //5 is the column where I was testing to paste the data 
  var lastColumn = sheet2.getLastColumn();
  sheet2.getRange(sheet2.getLastRow() + 1,1,vs.length,vs[0].length).setValues(vs);
  Drive.Files.remove(convertedSpreadsheetId); 
}