Expression to get Table Data "Source Id"

[takuya_miyai]

Hi evreyone,

Is there an Expression that retrieves the Source Id?

 

I know there have been many requests for this in the past, but this is because Apps Script integration is now available and there are more and more cases where people want to use data sources from spreadsheets.

Note that when Google Drive is used as the data source, the parent folder ID can be obtained by passing the file ID to the Apps Script as shown below.
This will be the Table Data Source Id in AppSheet.

function getSourceId(fileId) {
  const file = DriveApp.getFileById(fileId);
  const parentsFolder = file.getParents();

  Logger.log(parentsFolder.next().getId());
}

I want to achieve the same thing in the Google sheet.

Thanks,

@carlinyuen 
@nico 

@Koichi_Tsuji 

[Marc_Dillon]

Not to my knowledge.

[Kirk_Masden]

I would like to have something like this but I don't think it exists. 

Ironically, it comes up in situations where its utility is unclear to me.  In the following post I asked why the appGuidString (another word for the string that identifies the app ID) is used in error messages:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Why-is-the-appGuidString-used-in-error-messages...

[takuya_miyai]

Thanks @Marc_Dillon @Kirk_Masden 
If you two don't know, it seems difficult to get it from AppSheet.

@nico 
@carlinyuen 
The use case is that when AppsScript called from AppSheet writes the result of the execution, the ID of the sheet to which it writes is passed along.

There is a workaround to use the AppSheet API, but it is not a complete no-code,...
I wish there was a better way to get the sheet ID on the AppSheet Editor, as @Kirk_Masden -sensei mentioned.

[carlinyuen]

Thanks, Takuya, that's a helpful request and I can see the use cases for it especially if you want to dynamically pass which sheet/table to update.  @Rachelgmoore and @Arthur_Rallu FYI, we'll track this in our feature requests for expressions improvements.

[takuya_miyai]

Thanks @carlinyuen ,

Thank you also for the addition to the Feature request.
I think it would be desirable for many App Creators to be able to access the IDs of their data sources within the AppSheet Editor.

In addition, it seems that my current problem can be avoided with Graham's Tips.

[graham_howe · Accepted solution]

Why not store the ID in a tab in the Google Sheet, then you could access it as a table in AppSheet. If you wanted to have the cell automatically update (something that would really only be useful if you were likely to copy the Sheet) then you could add a script like the following to the spreadsheet:

function myID() {
   var thisURL = SpreadsheetApp.getActiveSpreadsheet().getUrl();
   var regex = new RegExp("\/d\/(.+)\/edit");
   var res = regex.exec(thisURL);
   return res[1];
}

[carlinyuen]

Neat trick, Graham! You might also be able to use the .getId() function directly to get the id without having to parse through the url.

https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getId()

[takuya_miyai]

Hi @graham_howe @carlinyuen ,
Is it something like this?

function myId() {
   const thisId = SpreadsheetApp.getActiveSpreadsheet().getId();
   return thisId;
}

Thanks for a simple, brilliant trick and follow up!🤗

[carlinyuen]

Yes, check if it works as you intend to though. My understanding is that
getActiveSpreadsheet() only works if there is an associated spreadsheet
with the script, so it may only work as a script that is directly attached
to the Sheet that you are using as your datasource. In that situation, you
may need to put that code in the script that is embedded into your Sheet to
store the ID into the appropriate column you want, and then you have the
separate Apps Script to do more functionality that is not necessarily
attached to a Sheet.

[takuya_miyai]

Thanks @graham_howe ,

Well, this is a good idea.
After a little experimentation, the Script is carried over to the new sheet even when the app is copied. I think this is a very good behavior.🤗