If I understand correctly you want to return .css file where .scss file exist. Here is the gulpfile.js code.

'use strict';
var gulp = require('gulp');
var sass = require('gulp-sass');
var autoprefixer = require('autoprefixer');
var postcss = require('gulp-postcss');
var paths = {
    styles: {
        src: 'modules/**/*.scss',
        dest: 'modules'
    }
}
function scss() {
    return gulp.src(paths.styles.src)
        .pipe(sass().on('error', sass.logError))
        .pipe(sass({ outputStyle: 'compressed' }))
        .pipe(postcss([autoprefixer()]))
        .pipe(gulp.dest(paths.styles.dest));
}
exports.scss = scss
function watch() {

    scss()

    gulp.watch(paths.styles.src, scss);
}
exports.watch = watch

package.json file need devDependencies and browserlist. it will be like this.

"devDependencies": {
    "autoprefixer": "^9.7.4",
    "gulp": "^4.0.2",
    "gulp-postcss": "^8.0.0",
    "gulp-sass": "^4.0.2"
  },
  "browserslist": [
    "last 71 version"
  ],

use $ gulp watch running your task.

Your folder structure like this

Themes
    modules
        hero
            hero.html
            hero.scss
        header
            header.html
            header.scss
        footer
            footer.html
            footer.scss
    package.json
    gulpfile.js

It will return

Themes
    modules
        hero
            hero.html
            hero.css
            hero.scss
        header
            header.html
            header.css
            header.scss
        footer
            footer.html
            footer.css
            footer.scss
    package.json
    gulpfile.js

Hope this help!

Try adding ./ to your source path:

return gulp.src('./app/scss/**/*.scss')
...

This tells it to start from the current directory, relative to the gulpfile.

Edit - you'll also need the same thing for the destination path:

  .pipe(gulp.dest('./app/css'));