0

I have an Excel file with 3 tabs (Sheet1, Sheet2, Sheet3) attached to a Gmail message and I would like these to copy to a Google Sheet file with 3 tabs (SheetA, SheetB, SheetC). The problem I am facing is that my script dumps the data from Excel Sheet 1 and repeats it on all 3 of my Google Sheet tabs. How do I adjust my script so I can copy data from all 3 tabs over?

function MyFunction() {
 
 var selectedSheets = ["SheetA","SheetB","SheetC"]; // select the sheets you want to run the function for
 var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets

  var sheet1= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetA");
  
  sheet1.clearContents();
  sheet1.clearFormats();

 var sheet2= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetB");
  sheet2.clearContents();
  sheet2.clearFormats();

var sheet3= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetC");
  sheet3.clearContents();
  sheet3.clearFormats();

  var thread = GmailApp.search('from:[email protected] subject:"Email Subject Test"', 0, 1)[0];
  
  var attachment = thread.getMessages()[thread.getMessageCount() - 1].getAttachments();
  var xlsxBlob = attachment[0]; //gets the attachment blob
  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id; //uploads the attachment to gdrive
  
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];  //opens the file in the background to access data
  var data = sheet.getDataRange().getValues(); //gets the data and stores it under 'data'
  sheet1.getRange(1,1,data.length,data[0].length).setValues(data); //like in the CSV function too uploads the data to our gsheet

  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];  //opens the file in the background to access data
  var data = sheet.getDataRange().getValues(); //gets the data and stores it under 'data'
  sheet2.getRange(1,1,data.length,data[0].length).setValues(data); //like in the CSV function too uploads the data to our gsheet

  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];  //opens the file in the background to access data
  var data = sheet.getDataRange().getValues(); //gets the data and stores it under 'data'
  sheet3.getRange(1,1,data.length,data[0].length).setValues(data); //like in the CSV function too uploads the data to our gsheet

}
1

1 Answer 1

0

The problem is that the script is repeating the following code line 3 times:

var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];  

More specifically, the problem is that [0] is the same for the three lines. To quickly fix this, use [0], [1] and [2] respectively.

Below is the original script with the suggested changes applied:

function MyFunction() {
 
 var selectedSheets = ["SheetA","SheetB","SheetC"]; // select the sheets you want to run the function for
 var sheets = SpreadsheetApp.getActive().getSheets(); // get all sheets

  var sheet1= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetA");
  
  sheet1.clearContents();
  sheet1.clearFormats();

 var sheet2= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetB");
  sheet2.clearContents();
  sheet2.clearFormats();

var sheet3= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SheetC");
  sheet3.clearContents();
  sheet3.clearFormats();

  var thread = GmailApp.search('from:[email protected] subject:"Email Subject Test"', 0, 1)[0];
  
  var attachment = thread.getMessages()[thread.getMessageCount() - 1].getAttachments();
  var xlsxBlob = attachment[0]; //gets the attachment blob
  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlsxBlob).id; //uploads the attachment to gdrive
  
  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0];  //opens the file in the background to access data
  var data = sheet.getDataRange().getValues(); //gets the data and stores it under 'data'
  sheet1.getRange(1,1,data.length,data[0].length).setValues(data); //like in the CSV function too uploads the data to our gsheet

  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[1];  //opens the file in the background to access data
  var data = sheet.getDataRange().getValues(); //gets the data and stores it under 'data'
  sheet2.getRange(1,1,data.length,data[0].length).setValues(data); //like in the CSV function too uploads the data to our gsheet

  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[2];  //opens the file in the background to access data
  var data = sheet.getDataRange().getValues(); //gets the data and stores it under 'data'
  sheet3.getRange(1,1,data.length,data[0].length).setValues(data); //like in the CSV function too uploads the data to our gsheet

}
1
  • 1
    Thank you so much Ruben !! It worked :)
    – Andrea B
    Jun 22, 2021 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.