When an email with a particular subject line arrives in my O365 (Outlook 365) inbox, I need to extract data from an attached .xlsx and push it all to an existing Google Sheet. Or more precisely: overwrite the existing Google Sheet.
What I do not want to do is create a new Google Sheet every time.
I have another .xlsx export that I set up via an Azure fileshare and a Power Automate Desktop flow that runs a command line script to convert .xlsx to .csv and save to the fileshare folder overwriting existing file (eight of them actually). Needs to be .csv because reasons... Google likes it that way! Don't have option to download .csv from source, so need a file conversion step in the automation, unless you have a better way to do this with the source .xlsx file. My Google Sheet updates itself via a query function that pulls the data from static Azure fileshare URLs for each .csv file. Thus I can, in quite a roundabout way, update an existing Google Sheet without saving as a new Sheet. This means that my existing logic in other Sheets can continue working with the same reference. There's probably lots of ways to accomplish this, but I feel I'm making it more complicated than it has to be.
Here's an example of how I grab the .csv data from Azure fileshare using IMPORTDATA nested within QUERY:
=QUERY(IMPORTDATA("https://<Azure Storage Account>.file.core.windows.net/<Azure Fileshare>/<Filename>.csv"&Sheet1!B31),"select * order by Col1 asc", 1)
where B31 is the SAS token for the fileshare.
For reasons I can't recall, ^that does not work with .xlsx and I have no desire to sort out how to work with Excel files properly in Google Drive and Google Sheets. I am somewhat entrenched into the Google cloud, but also have a full Microsoft cloud subscription. Totally willing to abandon Google Sheets in favor of Excel desktop or Excel in the cloud, and drop Google Drive in favor of OneDrive or Sharepoint or whatever. But that's exactly the problem! Microsoft's cloud is so sprawling and complicated, there's almost too many ways to do the same thing. Getting where I got so far with Drive and Sheets was far easier to figure out than similar experiences with Excel and OneDrive... lol. But Power Automate and PAD have been wonderful to work with, aside from the lag. Really easy to figure out. So I'm willing to fully take the plunge into the Microsoft cloud if that is what it takes to process this data properly.
However, simply finding a reasonable way to automate the overwrite of an existing Google Sheet (without breaking the existing functions and data connections) is my main goal at the moment. Essentially I just need to trigger on a daily email to push this spreadsheet data to an existing Sheet. As long as that data winds up in a sheet with a URL or GID that I know already, my existing logic will keep working. If my data is going to a new Sheet with a different URL/GID every time, that makes this a whole lot harder to deal with symbolically.
The end result is that I need this data to be compared between 3+ different sources which might have discrepancies and disagreements between them. I need to cross reference these -possibly disparate- data streams and programmatically "decide" what the ultimate truth is, then push certain key values to QuickBooks Online via SaaSant integration.
By the way, here are the scripts which my PAD flow uses to convert the .xlsx files to .csv - it's actually using a LibreOffice command that runs via WSL to do the file conversion because I did not have an Excel license at the time I wrote the script, and it moves the .csv files to the local fileshare folder via normal PowerShell commands:
cd C:UsersPC'.Google Drive'SyncData; wsl.exe "/mnt/c/Program Files/LibreOffice/program/soffice.exe" --convert-to csv *.xlsx
cd ; cd C:UsersPC'.Google Drive'SyncData; Move-Item -Path .*.csv -Destination X: -Force;
Below is a solution to similar question - this probably has nuggets of useful stuff, but I am trying to avoid Google App Script at all costs! Had a bad experience with making a Sheets script to generate URLs to all documents incoming to a particular Drive folder... Script worked great - once! The triggers I would set up to automatically run the script upon receipt of documents would delete themselves after running a single time! I cannot figure out how to automatically generate URLs to Google Drive files and save them in a Sheet, via Google Sheets logic alone anyway. Seems to require a script or some other advanced solution. Since I had such frustrations with that, I'd prefer to avoid going the Google AppScript route if possible.
COMPLICATED SOLUTION
Anyway, maybe I'm way overthinking this and maybe someone has an easy solution that does not require static URLs in an Azure fileshare or any complicated scripting??? Thanks in advance!
