App Script and Google Sheets - Integration - Delphi 7, Indy 10 - Error "HTTP/1.0 404 Not Found"

[tetycardoso]

I would be forever thankful if someone could help solving this issue, I'm trying to solve it for ten days now without success.

I'm working on a feature in my Delphi 7 application (using Indy 10) that needs to be integrated with Google Sheets and Google Scripts. In summary, I need to programmatically send a Google Sheets to my Google Drive and, in sequence, to create a App Script and bind it to my Google Sheets doc, because I need that my Google Sheets to perform the script.

My code is working regarding the following: (1) it is creating the spreadsheet Google Sheets in the desired google drive's folder; (2) It is creating the Script Project and (3) It is creating the Script Project as a contêiner-bounded script. When I access the Sheets through my browser, I can find the Project binded to the Sheets (opening the latter and acessing the App Script in Menu -> Extensions).

However, that said, my code is not working when I tried to add a File (.gs) to the Project, it is returning the following error: "HTTP/1.0 404 Not Found".

I already tried a lot of suggestions and already verified the Script-ID and a lot of other tries bue none of them seems to work.

Following I'm sharing the relevant portion of your code, especially the sections where I am:

- Generating the .gs file content.

- Making the API requests for creating the Apps Script project and associating it with the Google Sheets file.

- Configuring and sending HTTP requests using Indy 10.

Notes

- The actual content of my variable named “IdDaPlanilha” is the Script ID. I corrected this during the development mas have not yet altered the variable’s name;

- I’m using Postman to get the Access Token, with following scope: “https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/script.projects https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/script.deployments https://www.googleapis.com/auth/drive.file”

*******

function TCadCompras_Cotacoes.EnviarParaGoogleDrive( pAccessToken, pCaminho : string ; pPlanilha : ShortString ) : string;

var

  HTTP: TIdHTTP;

  SSL: TIdSSLIOHandlerSocketOpenSSL;

  FormData: TIdMultiPartFormDataStream;

  Response, MetadataStream: TStringStream;

  JSONResponse: string;

  FileContent: TMemoryStream;

  FileURL: string;

  Boundary, Body, Metadata : string;

  vRes : string;

  PermissionData: TStringStream;

  PermissionPayload: string;

  RespP : TStringStream;

  vScriptCode  : String  ;

  IdDaPlanilha : string  ;

  IdDaPasta : string  ;

  IdDoScript  : string  ;

begin

   pAccessToken:= Trim( Observacao.text );

   if 1=1 then

   Begin

      Result := '';

      HTTP := TIdHTTP.Create(nil);

      SSL := TIdSSLIOHandlerSocketOpenSSL.Create(nil);

      Response := TStringStream.Create('');

      MetadataStream := TStringStream.Create('');

      FileContent := TMemoryStream.Create;

      try

       // Loads the content of the Sheets on FileContent

         FileContent.LoadFromFile( pCaminho + pPlanilha );

       // Defines the boundary to separate parts of the request

         Boundary := '-------314159265358979323846';

       // Builds  the metadata JSON (name and mimeType of the file)

              IdDaPasta := '1D8Iywwgzqyv1bIQ41owNzwaHb4YTTe67';

         Metadata := '--' + Boundary + #13#10 +

                     'Content-Type: application/json; charset=UTF-8' + #13#10#13#10 +

                     '{"name": "' + pPlanilha + '", "parents": ["' + IdDaPasta + '"] , "mimeType": "application/vnd.google-apps.spreadsheet"}' + #13#10 +

                     '--' + Boundary + #13#10 +

                     'Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' + #13#10#13#10;

         // Adds the metadata JSON on MetadataStream

         MetadataStream.WriteString(Metadata);

         MetadataStream.Position := 0;

         // Copies the MetadataStream to Response

         Response.CopyFrom(MetadataStream, MetadataStream.Size);

         // Copies the content of the spreadsheet (binary) to Response

         FileContent.Position := 0;

         Response.CopyFrom(FileContent, FileContent.Size);

         // Adds the final part of the boundary

         Response.WriteString(#13#10 + '--' + Boundary + '--' + #13#10);

         // Configures the HTTP

         SSL.SSLOptions.SSLVersions :=  [sslvSSLv23];

         HTTP.IOHandler := SSL;

         HTTP.Request.ContentType := 'multipart/related; boundary="' + Boundary + '"';

         HTTP.Request.CustomHeaders.AddValue('Authorization', 'Bearer ' + pAccessToken);

         // Sends the request and captures the response

         vRes := HTTP.Post('https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart', Response);

         IdDaPlanilha := JSON_Conteudo( vRes , 'id');

         // Constrói a URL do arquivo no Google Drive

         if IdDaPlanilha <> '' then

         Begin

            IdDoScript := EnviarScript( pAccessToken , IdDaPlanilha );

            vScriptCode :='function myFunction() {  SpreadsheetApp.getUi().alert("Alo Mundo") }';

            AddAppsScriptToGoogleSheet( vScriptCode, pAccessToken , IdDoScript );  //  <<<---- Liberar aqui....

            //    Result := FileURL;

            Result := IdDaPlanilha;

         end

         else

         begin

            ShowMessage('ID não encontrado na resposta JSON');

         end;

      finally

         HTTP.Free;

         SSL.Free;

         Response.Free;

         MetadataStream.Free;

         FileContent.Free;

         PermissionData.Free;

         RespP.Free;

      end;

   end; // 1=2

end;

function TCadCompras_Cotacoes.EnviarScript( pAccessToken, pIdDaPlanilha : string ) : string;

var

  IdHTTP: TIdHTTP;

  RequestBody, Response: TStringStream;

  vRes, URL, AccessToken, JSONPayload: string;

  JSONBody22: TStringStream;

  xxx2 : TStringStream;

  xxx: string ;

  IdDoScript : string ;

begin

  IdHTTP := TIdHTTP.Create(nil);

  try

    // Configures the authentication’s header

    IdHTTP.Request.CustomHeaders.Values['Authorization'] := 'Bearer ' + pAccessToken;

    IdHTTP.Request.ContentType := 'application/json';

    // URL of the Google Apps Script’s API

    URL := 'https://script.googleapis.com/v1/projects';

    // Rquest’s body JSON

    JSONPayload := '{"title": "Meu Script OnEdit 12", "parentId": "' + pIdDaPlanilha + '"}';

    // Creates the request’s body with codification UTF-8

    RequestBody := TStringStream.Create(UTF8Encode(JSONPayload)); // Codifica como UTF-8

    Response := TStringStream.Create('');

    try

      // Sends the POST request

      IdHTTP.Post(URL, RequestBody, Response);

      IdDoScript := JSON_Conteudo( Response.DataString , 'scriptId');

      result := IdDoScript;

    finally

      RequestBody.Free;

      Response.Free;

    end;

  except

    on E: Exception do

      ShowMessage('Erro: ' + E.Message);

  end;

  IdHTTP.Free;

end;

procedure TCadCompras_Cotacoes.AddAppsScriptToGoogleSheet(const ScriptCode, pAccessToken , pIdDaPlanilha: string);

var

  HTTP: TIdHTTP;

  SSL: TIdSSLIOHandlerSocketOpenSSL;

  JSONBody, Response: string;

  AccessToken: string;

  qID : String  ;

  url : String  ;

begin

  HTTP := TIdHTTP.Create(nil);

  SSL  := TIdSSLIOHandlerSocketOpenSSL.Create(nil);

  try

    HTTP.IOHandler             := SSL;

    HTTP.Request.ContentType   := 'application/json';

    HTTP.Request.CustomHeaders.AddValue('Authorization', 'Bearer ' + pAccessToken);

    // Creates the JSON for the Apps Script

    JSONBody := Format(

      '{"files": [{"name": "CodeY1", "type": "SERVER_JS", "source": "%s"}]}',

      [ScriptCode]

    );

    qid := pIdDaPlanilha;//    16/01 14:02 não deu resultado

    // Sends the request to create the Apps Script

    try

       url := 'https://script.googleapis.com/v1/projects/'+qId+'/content';

       Response := HTTP.Post( URL , TStringStream.Create(JSONBody));

    except

       on e:exception do

          Showmessage('Erro.: '+e.message );

    end;

  finally

    HTTP.Free;

    SSL.Free;

  end;

end;