2

My question is very similar to this one, except I have formulas in my excel file which means all the data ends up being #error.

Importing XLSX file from the monthly e-mail (in Gmail) to the designated Google Sheet

Recap, I get an excel by email which I need to convert to google sheet where I already have a script which runs to pull data and then upload stock values.

Everything works well except for this one source where there are formulas in the excel file.

There's a few formulas in the excel sheet but only one column (P) I really need which has the below formula in it. It looks up the stock id to another sheet that I don't have. =IF(ISERROR(VLOOKUP(A2,'W:\Stock\Stockboard.xlsx]Stock'!$B:$N,13,FALSE)),0,VLOOKUP(A2,'W:\Stock\[Stockboard.xlsx]Stock'!$B:$N,13,FALSE))

Example File

The sheet opens in excel and excel understandably has the ability to not refresh and therefore not show errors. When I manually export to csv it's also fine.

This is the code at the moment, which is the same as the original post.

  var thread = GmailApp.search("from:(email) Stock Update has:attachment",0,1)[0];
  var message = thread.getMessages()[0];
  var xlFile = message.getAttachments()[0];
  var convertedSpreadsheetId = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS}, xlFile).id;

  var sheet = SpreadsheetApp.openById(convertedSpreadsheetId).getSheets()[0]; // There is the data in 1st tab.
  var data = sheet.getDataRange().getValues();

  ss.getSheetByName("test").getRange(1,1,data.length, data[0].length).setValues(data);

  Drive.Files.remove(convertedSpreadsheetId); // Remove the converted file.
Marc V
  • 21
  • 3
  • Although I', not sure about your actual excel data from your question, if the cell has already had the error after the excel data was converted to Google Spreadsheet, the formula in the excel data might not be able to be used at Google Spreadsheet. If my understanding is correct, this might not be achieved by only Google's API. If I misunderstood your situation, I apologize. By the way, when you provide a sample excel file for replicating your issue, it might help users think of the solution and workarounds. At that time, please remove your personal information. – Tanaike Dec 02 '19 at 23:28
  • Thanks @Tanaike, I've added a link and a bit more description. My alternative is that I use Google Scripts to save the file from my email then use some sort of windows batch thing(?) to process it into and csv and then Google Scripts again to process after. I'm not familiar with batch stuff and just sounds messy. TIA – Marc V Dec 03 '19 at 02:58
  • Just looking at something else and wondering if Google API to call an external App (cloudconvert)? Even if not I see they have an api and guessing this is a workaround? Would be nice to not have to depend on a third party but if it works. – Marc V Dec 03 '19 at 04:57
  • Thank you for replying. I could understand about your excel file. In that case, because the file cannot be loaded, such issue occurs. In this case, I think that your issue cannot be resolved by Google Apps Script, because the import file cannot be loaded. By the way, although I cannot image about `an external App (cloudconvert)`, if `external App` supports the import file for excel file, your issue might be resolved. But about this, I cannot answer clearly. This is due to my poor skill. I apologize for this. – Tanaike Dec 03 '19 at 05:04

0 Answers0