How to import data in XLS format for Google Sheets with AppScrips?

[Ricardo Luiz A. Pimentel Oliveira]

I'm having a hard time automating the update of reports in Google Sheets using data from Excel. The only solution I found was to use plug-ins like (Sheetsgo), however as it is a Corporate project the budget is restricted, so in no way could I indicate a solution using a Script in Google Sheets?

Thank you all!

[Alan Wells]

Sorry to hear about the restricted Corporate budget.  Please express my sympathies to upper management and the shareholders.  Have you thought about doing some fund raising?  You could organize your fellow co-workers to bake cookies, and have the children sell them in school.

I'm just trying to think about how to help you out.

If you can provide some details, I'm sure someone could provide some information.  There are probably many steps in your process, and literally millions of things that could go wrong.  We can't know every possible difficulty that you are having.  Can you get the data from Excel to Sheets?

[Darren D'Mello]

If possible you can share the XLS file sample.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/609379b8-7962-4bb1-b650-95212892d6dc%40googlegroups.com.

[Steve Webster]

There are a couple of ways to handle this. One method we have used with a customer is the common denominator of CSV format. 

The workflow would be export from EXCEL to CSV file, and then have an Apps Script open that file, convert to array, filter and if conditions and then update existing Google spreadsheet rows and columns. Tip: you can skip the step of converting the entire Excel to Google Sheets and then filter and if conditions -- by reading CSV into an array.

More specifically, the Excel could have a VBA script to export the CSV, have it stored in a shared Drive folder, and then an Apps Script looks via a timed trigger to look for the new CSV file.

Kind Regards,

Steve Webster

SW gApps LLC, President 

GDE (Google Developer Expert)

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHeo4XhhWFCAV2ZWH1WJTLWjpcPTdeB7m_AYiBTcRwTUMzAbsQ%40mail.gmail.com.

[Ricardo Luiz A. Pimentel Oliveira]

Hello!

thanks for your comment, the situation is really unfortunate, hahahahaha.

But to be more specific:

Currently the data is extracted daily from an ERP directly to a folder in Excel format, however I need to use the data in a Google spreadsheet to then present the views in Data Studio, however I need to keep the data up to date and I need to automate this activity update data on Google spreadsheets.

Thanks again!

[Ricardo Luiz A. Pimentel Oliveira]

I'm sorry I can't share this spreadsheet.

Thanks!

On Monday, April 27, 2020 at 1:06:36 PM UTC-3, miscellaneousmailer wrote:

If possible you can share the XLS file sample.

On Mon, Apr 27, 2020, 9:29 PM Alan Wells <aj.a...@gmail.com> wrote:

Sorry to hear about the restricted Corporate budget.  Please express my sympathies to upper management and the shareholders.  Have you thought about doing some fund raising?  You could organize your fellow co-workers to bake cookies, and have the children sell them in school.

I'm just trying to think about how to help you out.

If you can provide some details, I'm sure someone could provide some information.  There are probably many steps in your process, and literally millions of things that could go wrong.  We can't know every possible difficulty that you are having.  Can you get the data from Excel to Sheets?

On Monday, April 27, 2020 at 11:43:25 AM UTC-4, Ricardo Luiz A. Pimentel Oliveira wrote:

I'm having a hard time automating the update of reports in Google Sheets using data from Excel. The only solution I found was to use plug-ins like (Sheetsgo), however as it is a Corporate project the budget is restricted, so in no way could I indicate a solution using a Script in Google Sheets?

Thank you all!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Ricardo Luiz A. Pimentel Oliveira]

Hi Webster,

thanks for the tip, i will try again with a CSV file.

On Monday, April 27, 2020 at 1:26:45 PM UTC-3, Steve Webster wrote:

There are a couple of ways to handle this. One method we have used with a customer is the common denominator of CSV format. 

The workflow would be export from EXCEL to CSV file, and then have an Apps Script open that file, convert to array, filter and if conditions and then update existing Google spreadsheet rows and columns. Tip: you can skip the step of converting the entire Excel to Google Sheets and then filter and if conditions -- by reading CSV into an array.

More specifically, the Excel could have a VBA script to export the CSV, have it stored in a shared Drive folder, and then an Apps Script looks via a timed trigger to look for the new CSV file.

Kind Regards,

Steve Webster

SW gApps LLC, President 

GDE (Google Developer Expert)

Google Product Expert in: Google Apps Script, Drive, and Docs 

Google Vendor (2012-2013) || Google Apps Developer Blog Guest Blogger 

Add-ons: Text gBlaster 

On Mon, Apr 27, 2020 at 12:06 PM Darren D'Mello <miscellan...@gmail.com> wrote:

If possible you can share the XLS file sample.

On Mon, Apr 27, 2020, 9:29 PM Alan Wells <aj.a...@gmail.com> wrote:

Sorry to hear about the restricted Corporate budget.  Please express my sympathies to upper management and the shareholders.  Have you thought about doing some fund raising?  You could organize your fellow co-workers to bake cookies, and have the children sell them in school.

I'm just trying to think about how to help you out.

If you can provide some details, I'm sure someone could provide some information.  There are probably many steps in your process, and literally millions of things that could go wrong.  We can't know every possible difficulty that you are having.  Can you get the data from Excel to Sheets?

On Monday, April 27, 2020 at 11:43:25 AM UTC-4, Ricardo Luiz A. Pimentel Oliveira wrote:

I'm having a hard time automating the update of reports in Google Sheets using data from Excel. The only solution I found was to use plug-ins like (Sheetsgo), however as it is a Corporate project the budget is restricted, so in no way could I indicate a solution using a Script in Google Sheets?

Thank you all!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/609379b8-7962-4bb1-b650-95212892d6dc%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.

[Alan Wells]

I don't work with Excel or Data Studio, so hopefully someone else can help.

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/14999098-98e3-4d2e-ba29-691eca819adf%40googlegroups.com.

[Ricardo Luiz A. Pimentel Oliveira]

Thanks!

Atenciosamente/Best regards,

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAK4Rc%2BcX9UN25wEw7O2t0toiC_OVE8Ef8J6JGdHLyWOSCj17LQ%40mail.gmail.com.

[Martin Molloy]

Sheetgo would cost you $228 a year. My Sheets add-on will do this for you. It costs $144 a year. (and it's better - but I'm biased)  It's called SheetsIE if you want to give it a try.

Martin

--

[Salani, Nicola]

Try this: https://www.labnol.org/code/20500-convert-microsoft-excel-xlsx-to-google-spreadsheet 

Nico

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2Pgqxq%3D%3DDerL5jc9S7vePjS8Py6K0uOtwhv4FLU_DjAJMQ%40mail.gmail.com.

[Ricardo Luiz A. Pimentel Oliveira]

Hi Molloy,

I will test your resource.

Thanks!

Atenciosamente/Best regards,

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAO8s2Pgqxq%3D%3DDerL5jc9S7vePjS8Py6K0uOtwhv4FLU_DjAJMQ%40mail.gmail.com.

[Ricardo Luiz A. Pimentel Oliveira]

Thanks I will Try!

Atenciosamente/Best regards,

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAGvD0yMMdP4qLBFaMVx_WH0GnD%3D2DxNX0WyAL_eQNeKsU%3D6-Qw%40mail.gmail.com.