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

Google Cloud Cloud Foundations & Onboarding
1

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 have 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 my 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 and have already checked the scopes.

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;