Azure Functions - The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

The Problem

I am trying to create an Azure function leveraging .NET Core 2.2 that accesses a Google Sheet via the Google Sheets API, calls the data, and inserts it into a SQL DB also hosted in Azure.

Here's the error I'm:

This after following this guide.

Note that all the packages are restored.

The code looks roughly like this:

#r "D:\home\site\wwwroot\bin\Google.Apis.Sheets.v4"
#r "D:\home\site\wwwroot\bin\Google.Apis.Auth.OAuth2"
#r "D:\home\site\wwwroot\bin\Google.Apis.Sheets.v4"
#r "D:\home\site\wwwroot\bin\Google.Apis.Sheets.v4.Data"
#r "D:\home\site\wwwroot\bin\Google.Apis.Services"
#r "D:\home\site\wwwroot\bin\System.Data.SqlClient"

using System;
using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using System;
using System.Collections.Generic;
using System.IO;
using System.Data.SqlClient;


public static string DBConn { get; set; }
public static ILogger Log {get; set;}
public static void Run(TimerInfo myTimer, ILogger log)
{   
    Log=log;  
    DBConn="MySQLSrvrConnectionString";
    try
    {
        string spreadsheetId = "MyGoogleSheetId";
        SheetsService service = GetSheetService();
        if(service!=null)
        {
            DoSomethingFunc(GetInsertCommand(GetSheetVals(service,spreadsheetId)));
        }
    }
    catch (Exception ex)
    {
        Log.LogInformation($"Error ({DateTime.Now.ToLongDateString()}): {ex.Message}");
    }
    finally
    {
        Log.LogInformation($"Function Completed at: {DateTime.Now.ToLongDateString()}");
    }
}

I can provide more if need be.

I've also added a function.proj file:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Google.Apis.Sheets.v4" Version="1.40.3.1679" />
    <PackageReference Include="System.Data.SqlClient" Version="4.6.1" />
  </ItemGroup>

</Project>

Research Haven't found much, honestly.

• This is a similar error code, but it seems to a naming issue - having an apostrophe. My app is called "TopicsProvider"
• This talks about a webparts XML file. That's not part of my project at all.

Update 1

Paring the code back to its simplest form, I am able to get it to run with the Google API referenced in function.proj. This code works:

using System;
using System.Collections.Generic;
using System.IO;
using System.Data.SqlClient;
public static void Run(TimerInfo myTimer, ILogger log)
{
    log.LogInformation($"HELLO");
}

With function.proj like this:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
    <AzureFunctionsVersion>v2</AzureFunctionsVersion>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Google.Apis.Sheets.v4" Version="1.40.3.1679" />
  </ItemGroup>

</Project>

What fails is adding using Google.Apis.Sheets.v4; at the top. This gives:

error CS0246: The type or namespace name 'Google' could not be found (are you missing a using directive or an assembly reference?)

Adding #r "Google.Apis.Sheets.v4" or D:\home\site\wwwroot\bin\Google.Apis.Sheets.v4" (note that in the console, there's not a bin folder to begin with...not sure why) to the top, doesn't fix the issue, and changes the error:

2019-09-03T17:48:31.700 [Information] Script for function 'TimerTrigger1' changed. Reloading.
2019-09-03T17:48:31.976 [Error] run.csx(1,1): error CS0006: Metadata file 'Google.Apis.Sheets.v4' could not be found
2019-09-03T17:48:32.051 [Error] run.csx(7,7): error CS0246: The type or namespace name 'Google' could not be found (are you missing a using directive or an assembly reference?)
2019-09-03T17:48:32.075 [Information] Compilation failed.
2019-09-03T17:48:32.549 [Information] Executing 'Functions.TimerTrigger1' (Reason='This function was programmatically called via the host APIs.', Id=bce27519-0236-4754-ac6c-66ae83808801)
2019-09-03T17:48:32.615 [Information] Package references have been updated.
2019-09-03T17:48:32.615 [Information] Restoring packages.
2019-09-03T17:48:32.647 [Information] Starting packages restore
2019-09-03T17:48:36.701 [Information] Restoring packages for D:\local\Temp\32a465b3-8a19-46c8-a533-799a91e1ec09\function.proj...
2019-09-03T17:48:38.434 [Information] Generating MSBuild file D:\local\Temp\32a465b3-8a19-46c8-a533-799a91e1ec09\obj\function.proj.nuget.g.props.
2019-09-03T17:48:38.435 [Information] Generating MSBuild file D:\local\Temp\32a465b3-8a19-46c8-a533-799a91e1ec09\obj\function.proj.nuget.g.targets.
2019-09-03T17:48:38.461 [Information] Restore completed in 2.56 sec for D:\local\Temp\32a465b3-8a19-46c8-a533-799a91e1ec09\function.proj.
2019-09-03T17:48:38.772 [Information] Packages restored.
2019-09-03T17:48:38.996 [Warning] You may be referencing NuGet packages incorrectly. Learn more: https://go.microsoft.com/fwlink/?linkid=2091419
2019-09-03T17:48:39.053 [Error] Function compilation error

Microsoft.CodeAnalysis.Scripting.CompilationErrorException : Script compilation failed.

   at async Microsoft.Azure.WebJobs.Script.Description.DotNetFunctionInvoker.CreateFunctionTarget(CancellationToken cancellationToken) at C:\azure-webjobs-sdk-script\src\WebJobs.Script\Description\DotNet\DotNetFunctionInvoker.cs : 314

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at async Microsoft.Azure.WebJobs.Script.Description.FunctionLoader`1.GetFunctionTargetAsync[T](Int32 attemptCount) at C:\azure-webjobs-sdk-script\src\WebJobs.Script\Description\FunctionLoader.cs : 55

   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()

   at async Microsoft.Azure.WebJobs.Script.Description.DotNetFunctionInvoker.GetFunctionTargetAsync(Boolean isInvocation) at C:\azure-webjobs-sdk-script\src\WebJobs.Script\Description\DotNet\DotNetFunctionInvoker.cs : 183
2019-09-03T17:48:39.122 [Error] run.csx(1,1): error CS0006: Metadata file 'Google.Apis.Sheets.v4' could not be found
2019-09-03T17:48:39.203 [Error] run.csx(7,7): error CS0246: The type or namespace name 'Google' could not be found (are you missing a using directive or an assembly reference?)
2019-09-03T17:48:39.256 [Error] Executed 'Functions.TimerTrigger1' (Failed, Id=bce27519-0236-4754-ac6c-66ae83808801)

Script compilation failed.

The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

As I understand it #r isn't necessary, not according to this answer. The packages should load on their own. Neither should I have to upload these files - they're nuget packages that should install automatically.

Update 2

Manually uploading the DLLs does not work. Selecting the file from "Upload", there's a moment where the interface acts like it's loading, but then nothing happens:

What am I missing? Is one of the packages not allowed? Is it a config issue?

Update:

I retry what you do and then it seems worked,

1.create an function.proj file

2.save the file and run:

 <Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
    <AzureFunctionsVersion>v2</AzureFunctionsVersion>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Google.Apis.Sheets.v4" Version="1.40.3.1679" />
    <PackageReference Include="System.Data.SqlClient" Version="4.6.1" />
  </ItemGroup>
</Project>

3.add code in .crx file:

#r "Newtonsoft.Json"

using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;

public static string DBConn = "123456";
public static ILogger Log = null;

public static async Task<IActionResult> Run(HttpRequest req, ILogger log)
{
    Log=log;  
    DBConn="MySQLSrvrConnectionString";
    try
    {
        string spreadsheetId = "MyGoogleSheetId";
        //SheetsService service = GetSheetService();
        //if(service!=null)
        //{
        //    DoSomethingFunc(GetInsertCommand(GetSheetVals(service,spreadsheetId)));
        //}
    }
    catch (Exception ex)
    {
        Log.LogInformation($"Error ({DateTime.Now.ToLongDateString()}): {ex.Message}");
    }
    finally
    {
        Log.LogInformation($"Function Completed at: {DateTime.Now.ToLongDateString()}");
    }
    string name = req.Query["name"];

    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    dynamic data = JsonConvert.DeserializeObject(requestBody);
    name = name ?? data?.name;

    return name != null
        ? (ActionResult)new OkObjectResult($"Hello, {name}")
        : new BadRequestObjectResult("Please pass a name on the query string or in the request body");
}

4.then compilation succeeded:

PS: The google .net client library does not have a method called get sheets service.

Original Answer:

This is my code:

public static class Function1
{
    public static string DBConn { get; set; }
    public static ILogger Log { get; set; }
    [FunctionName("Function1")]
    public static async Task<IActionResult> Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
        ILogger log)
    {
        Log = log;
        DBConn = "MySQLSrvrConnectionString";
        try
        {
            string spreadsheetId = "MyGoogleSheetId";

        }
        catch (Exception ex)
        {
            Log.LogInformation($"Error ({DateTime.Now.ToLongDateString()}): {ex.Message}");
        }
        finally
        {
            Log.LogInformation($"Function Completed at: {DateTime.Now.ToLongDateString()}");
        }
        return (ActionResult)new OkObjectResult($"");
    }
}

I delete the

SheetsService service = GetSheetService();
if(service!=null)
{
     DoSomethingFunc(GetInsertCommand(GetSheetVals(service,spreadsheetId)));
}

because i don't find which nuget package has these methods.

and this is my function.proj file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
    <AzureFunctionsVersion>v2</AzureFunctionsVersion>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Google.Apis" Version="1.40.3" />
    <PackageReference Include="Google.Apis.Auth" Version="1.40.3" />
    <PackageReference Include="Google.Apis.Auth.Mvc" Version="1.40.3" />
    <PackageReference Include="Google.Apis.Sheets.v4" Version="1.40.3.1694" />
    <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="1.0.28" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
</Project>

and then it works fine. I don't know which packages contain those methods because I don’t know much about Google API. I hope my answer will give you some help.