Unable to display xlsx file in browser

[gougou]

I created bot to create an xlsx file.

The path to the created file is stored in the column set for the file type.

When I click on the icon that appears as a link to the path on the app, the file does not appear in the browser, but is automatically downloaded locally.

How do I make it appear in the browser?
Or can I save the file locally by specifying the path?

(The template is created in xlsx format and placed in Dropbox)

[baba_sawane]

Hi gougou,

When you say "open in browser", do you mean open in web dropbox xlsx viewer ?

[gougou]

Hi baba,

Since the xlsx file will be created on google drive.
I would like to be able to view them on google drive.

[baba_sawane]

Hi,

so that you know,  you can reply using the answer blue button 😀

As far as I know you can't directly open in Drive xlsx files that you created through app . 

You need to get the Google Drive ID's of those files, then construct an URL to those files.

solution n°	in app (check the first answer, never tried, but worth the try as it seems simple)	through app script
1	https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Get-a-google-drive-file-ID-for-an-image-after-i... https://support.google.com/appsheet/answer/11905920?hl=en	https://www.googlecloudcommunity.com/gc/Tips-Tricks/Get-the-Google-Drive-File-IDs-automatically-and-... https://www.appsheet.com/templates/gets-the-google-drive-ID-and-places-it-back-into-the-data-source?...

I don't think we can specify a local path when downloading a file.

Hope it helps 

[gougou]

Thank you for your response.
I have implemented it with the link you provided,
I can't get a unique file_ID.

I don't know how to describe "collection of files name".

[filename][_ID] 

"SELECT or list dereference expression and should be a List/EnumList of Refs"

an error message appears.

[baba_sawane · Accepted solution]

First, create a new folder for your collection, then add this folder like you were adding a normal table by selecting collection of file (if it's not already done).

Collection of files name is the table name (which is your collection folder name).

Use this :  concatenate("https://drive.google.com/file/d/", select(collectiontablename[_ID], contains([Path], [_THISROW].[File])) 
update : you have to use https://docs.google.com/spreadsheets/d/ to be able to edit the file.

concatenate("https://docs.google.com/spreadsheets/d/ ", select(collectiontablename[_ID], contains([Path], [_THISROW].[File])) 

Using this formula, you select this table, and get the value of the file ID of each row , by filtering the Path column. The filter select values that contains your file name.

____

A collection of files is listing all the files contained in one folder -without subfolders- in your Drive. This is why you have to edit your file path in your automation of your generated xlsx inside this folder. 

If you want to get the URL to your file in the table where you generated it, use any(select(collectionfilestablename[URL], [File] = [_THISROW].[File] ) ))

[gougou]

Thanks to your help, we are able to get the ID of the collection file.
However, the characters seem to be garbled.
I assume I need to do some encoding, but how can I do that?

[baba_sawane]

Hi gougou, can you provide a screenshot ? 

I did not have to encode the url on my end, but for reference the encode formula is ENCODEURL() 

Maybe your filter is selecting multiple values.

[gougou]

It seems that if I specify _ID in the URL, it is garbled.

It seems that the _ID is prefixed with %20.

[gougou]

Sorry .😥
A space was inserted during concatenation. Resolved.
Thank you very much!😀

[baba_sawane]

Cool 😎 You can mark the answer as solution.

Have a nice week 🤠

[gougou]

Sorry for repeating myself,😭😭
I re-created the table for the collection and now I cannot get the paths ([Path]) in the collection.

I can only get up to one folder above the directory where the files are stored.
What could be the cause?

[baba_sawane]

I'm not sure if I understand correctly. [Path] should be generated automatically, I suggest you to delete and add it again to see if it changes.

If it's related, refer to this : 

A collection of files is listing all the files contained in one folder -without subfolders- in your Drive. This is why you have to edit your file path in your automation of your generated xlsx inside this folder | without any subfolder. 

[gougou]

I think I have specified the acition folder path correctly.

(In the apps folder)
"/Files/"

Can't I get the paths of files under the Files folder?

[baba_sawane]

You should be able to do that. 

• "Files/" in your automation
• any(select(Files[URL], [File] = [_THISROW].[File] ) )) to get the URL 

[gougou]

I'm sorry.

I don't know what Files[URL] means.

[baba_sawane]

I don't clearly understand the new issue 😅

If Files is the name of your collection folder, then it's also the name of the table. [URL] is the virtual column inside this table,  where you added the concatenate("https://docs.google.com/spreadsheets/d/ ", select(collectiontablename[_ID], contains([Path], [_THISROW].[File]))  formula. 

Let's say you generate your file inside a table named ReportWorkflow. Inside this table, your generated file path has to be inside the Files table ("Files/" ).

Inside ReportWorkflow, you can show the url to the xlsx by adding a new column "Sheet URL", and using the formula I suggested.

[gougou]

concatenate(" https://docs.google.com/spreadsheets/d/ ", select(collectiontablename[_ID], contains([Path], [_THISROW] )))

↑

Virtual columns in "collectiontablename"
You write in the formula for [URL], right?

any(select(collectiontablename[URL], [File] = [_THISROW].[File] ) ))

Do you put it in the formula for the file column you want to create in the "ReportWorkflow" table?

[File] = [_THISROW].[File]

I do not understand this part.
Does [File] mean collectiontable[File]?

[_THISROW].[File]

"ReportWorkflow" 

The path to the file you created?

[baba_sawane]

---

@gougou wrote:

concatenate(" https://docs.google.com/spreadsheets/d/ ", select(collectiontablename[_ID], contains([Path], [_THISROW] )))

↑

Virtual columns in "collectiontablename"
You write in the formula for [URL], right?

---

Yes.

---

any(select(collectiontablename[URL], [File] = [_THISROW].[File] ) ))

Do you put it in the formula for the file column you want to create in the "ReportWorkflow" table?

---

No, you put it in a new virtual column in this "ReportWorkflow" table. You can use it to access your spreedsheet from the same table where you created it, without having to interact with the collection table. 

---

[File] = [_THISROW].[File]

I do not understand this part.
Does [File] mean collectiontable[File]?

---

 [File] is the column named File in your collection table, and [_THISROW].[File] is the file column of the "ReportWorkflow" table. (it may led to confusion if you have a different name for that column).

[gougou]

select(collectiontablename[_ID], contains([Path], [_THISROW].[File])) 

One more thing.
I can only get a directory one level up in the file path.
Why is this?

any(select(collectiontablename[URL], collectiontablename[File] = [_THISROW].[File] ) ))

Cannot compare List with File in (collectiontablename[File] = [_THISROW].[File] )

I get the above error.

[gougou]

The file path specification was incorrect when creating the collection file,
directory has been resolved.
My apologies for repeating myself.

However, I do not know the cause of the error about the ANY syntax.
Can you please help me?

[baba_sawane]

If you are talking about your message below : 

any(select(collectiontablename[URL], collectiontablename[File] = [_THISROW].[File] ) ))

Cannot compare List with File in (collectiontablename[File] = [_THISROW].[File] )

Indeed, you are comparing a list of values with the value of the current row, there's a comma in the formula, it's : any(select(collectiontablename[URL], [File] = [_THISROW].[File] ) ))

So that you understand better the formula, let's name your generated file column [GeneratedFile].

It becomes :  any(select(collectiontablename[URL], [File] = [_THISROW].[GeneratedFile] ) )) 

Replace [GeneratedFile] with wathever is the name of the File column of your workflow table. 

___

The formula is looking in the collectiontable the list of rows that have the same value as your current file name and returns a list of [URL] values of those rows. Any() returns a unique item from that list and is there to remove the list format, because even if there's only one row that match your current file name, it will still be in a list format (which is not convenient as what you want is to return an URL). 

*can you use the reply button to make it easier to follow 😅

We'll get this through don't worry 😂 You can share a screenshot of your table if it does not work

About this :

I can only get a directory one level up in the file path.

It's the collection of files limitations. You can only use files from that folder exclusively, excluding files from subfolders. 

[gougou]

As you say, it is described as follows
But I get an error comparing list and text.

ANY(
SELECT(collectiontablename[URL],
collectiontablename[File] = [_THISROW].[GeneratedFile]))

※The directory matter has been resolved.

[baba_sawane]

As said you need to correct your formula, you have :

ANY(SELECT(collectiontablename[URL],collectiontablename[File] =[_THISROW].[GeneratedFile]))

But you need to remove the red part , it becomes :

>>ANY(SELECT(collectiontablename[URL],[File] =[_THISROW].[GeneratedFile]))

[gougou]

We were able to acquire it successfully!!
Thank you for your patience with us.
I can't thank you enough.
Thank you so much.