Query Builder for AW API?

I'm trying to find all my accounts with cost == 0.

 var query =
   'SELECT AccountDescriptiveName, Cost FROM ACCOUNT_PERFORMANCE_REPORT DURING LAST_7_DAYS WHERE Cost > 0';
 var recommendationResult = AdsApp.search(query, { apiVersion: 'v8' });

But I get this exception:

Exception: Could not identify resource for search query "SELECT AccountDescriptiveName, Cost FROM ACCOUNT_PERFORMANCE_REPORT DURING LAST_7_DAYS WHERE Metric.Cost == 0". Double-check your FROM clause. (file Code.gs, line 18)

I wanted to build it from scratch using the Query Builder but I haven't found ACCOUNT_PERFORMANCE_REPORT report (AW API) or it GA API equivalent.

Also, using the GAds connector to data studio. Is there a way to fetch the query behind some table?

ACCOUNT_PERFORMANCE_REPORT only gives you metrics for one single account, so it's not going to be appropriate for your use case. Generally there's no way to query multiple accounts at once in neither the Adwords API nor the Ads API.

If this is specifically about Data Studio and you have less than 50 accounts, then you can just add them all as a single data source. If there's more, you could set up a BigQuery transfer service as suggested by @Samuel (assuming all the accounts are under the same manager account), create a UNION ALL view of all the resulting Customer_<customer_id> tables and create a Data Studio source connected to that view.