If I understand correctly, you have a single spreadsheet with two sheets, and you want to add a link in a cell in Sheet A that switches the active view to Sheet B.

I believe the HYPERLINK function will solve your problem. The url you enter as first parameter to this function should be of the form "#gid=<sheet id>" where <sheet id> is the sheet id for Sheet B.

You can find the gid for Sheet B by switching to the sheet in the browser window, and look for the gid= term at the end of the URL. All Google Sheet URLs take the form https://docs.google.com/spreadsheets/d/<spreadsheetId>/edit#gid=<sheetId>

No way to turn of that I know of. So annoying.

You can work-around using normal copy-paste, then a search-and-replace.

1. Copy-Paste you content into the Google Doc
2. In a text-editor, enter a tab character then cut it to your clipboard
3. Back in Google Docs, highlight the content you wish to fix
4. Hit Ctrl + H to open Find and replace dialogue
5. Paste the tab character into the Find field
6. Insert 4 space characters into the Replace with field
7. Click Replace all

Apparently it is possible using a batchUpdate: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate

With one of the requests being of the form of: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#AddSheetRequest

For example, in order to add a new empty sheet with the name "FOO", one can send a batchUpdate like this one:

sheets.spreadsheets.batchUpdate(
    {
        auth: authClient,
        spreadsheetId: spreadsheetId,
        resource: {
            requests: [
                {
                    'addSheet':{
                        'properties':{
                            'title': 'FOO'
                        }
                    } 
                }
            ],
        }
    },
    function(err, response) {
        if (err) return callback('The API returned an error: ' + err);
        console.log("success: ", response);
});