Is it possible to turn that into '<script setup>' ? I try many ways but missing something maybe

It helps to break the problem down into smaller problems that can be tackled individually...

Component registration

Components are automatically registered upon importing their definition in a <script setup>, so registering Product is trivial:

<script setup>
import Product from '@/components/Product.vue' // locally registered
</script>

Data properties

Reactive data properties are declared as ref (or reactive):

<script setup>
import { ref } from 'vue'

const products = ref([])
const error = ref('')

// to change the value of the `ref`, use its `.value` property
error.value = 'My error message'
products.value = [{ name: 'Product A' }, { name: 'Product B' }]
</script>

Alternatively, you can use the new/experimental Reactivity Transform in <script setup>, which globally defines the reactivity APIs, prefixed with $ (e.g., $ref for ref), and avoids having to unwrap refs via .value:

<script setup>
let products = $ref([])
let error = $ref('')

// assign the values directly (no need for .value)
error = 'My error message'
products = [{ name: 'Product A' }, { name: 'Product B' }]
</script>

created lifecycle hook

The <script setup> block occurs in the same timing as the setup hook, which is also the same timing as the created hook, so you could copy your original hook code there. To use await, you could either wrap the call in an async IIFE:

<script setup>
import ProductAPI from '@/api/products.api'
import { ref } from 'vue'

const products = ref([])

;(async () => {
  products.value = await ProductAPI.fetchAll()
})()
</script>

...or create an async function that is called therein:

<script setup>
import ProductAPI from '@/api/products.api'
import { ref } from 'vue'

const products = ref([])

const loadProducts = async () => products.value = await ProductAPI.fetchAll()
loadProducts()
</script>

Component name

There's no equivalent Composition API for the name property, but you can use a <script> block in addition to <script setup> in the same SFC to contain any props that are not supported by the Composition API:

<script setup>
⋮
</script>

<!-- OK to have <script> and <script setup> in same SFC -->
<script>
export default {
  name: 'products',
}
</script>

Alternatively, you can specify the component's name via its filename. This feature was added in v3.2.34-beta.1. For example, a component with <script setup> whose filename is MyComponent.vue would have a name of MyComponent.

demo

you should import the component before registering it.

<script>
import ProductsAPI from '../api/products.api';
import ProductComp from './components/Product.vue'

export default {
    name: 'products',

    components: {
        'product': ProductComp
    },

    data() {
        return {
            products: [],
            error: '',
        };
    },

    async created() {
        this.products = await ProductsAPI.fetchAll();
    },
};

Using script setup would look something like this.

 <script setup>
   import ProductsAPI from '../api/products.api';
   import Product from './components/Product.vue'

   let error = '';
   let products = await ProductsAPI.fetchAll();
</script>

The problem is specific to composition API, not specifically script setup. As long as a component is properly written, script can, with few exceptions, be easily transformed to script setup by extracting the content of setup while preserving imports and setup return values.

The lifecycle in composition API has close but not direct counterpart to created hook. And the problem is that created was initially misused. Vue heavily borrows from React and has a similar lifecycle, the latter has strict rules regarding the use of side effects on component creation. All side effects are supposed to be performed later when a component has been already mounted.

Vue doesn't have the same technical limitations as React, but some concerns are also applicable. There are just not enough reasons to do side effects in created, so they belong to mounted. There are little to no advantages to have them in created, while the disadvantages are that initial rendering is blocked by a work that could be done later on component mount, and the side effects that are done too early on component creation may result in problems in SSR due to its semantics, and then there are problems with script setup. Also there is clear semantics for mounted and unmounted hooks, the latter may do a cleanup for side effects that occur in the former.

In both script setup and script with setup function, it can be:

let products = ref([]);

onMounted(async () => {
  products.value = await ProductsAPI.fetchAll();
});

TL;DR: It's a good practice to use mounted and not created lifecycle hook for asynchronous side effects. mounted is available in script setup.