Help - Google Sheets and App Script integration (Delphi 7, Indy 10 - "404 Error - Not Found")

Google Cloud Cloud Foundations & Onboarding
1

Hi everyone,

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 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. I don’t if there is something wrong with the code, or if it is a matter of acesses and permissions…

Notes:

  • Please consider that the variable that I named “IdDaPlanilha” is actually carrying the Script ID. I correct this during the programming but have not still altered the name of the variable.

  • I’m using Postman to get the Access Token. The scope of the access token is configured as following:

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

  • The Google API is activated in my google account, so is the configuration of the OAuth .

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.

Here it is:

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 from the Sheets to the FileContent
FileContent.LoadFromFile( pCaminho + pPlanilha );

// Defines the boundary to separate the parts of the request
Boundary := ‘-------314159265358979323846’;

// Puts together 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 JSON metada in the MetadataStream
MetadataStream.WriteString(Metadata);
MetadataStream.Position := 0;

// Copies the MetadataStream to the Response
Response.CopyFrom(MetadataStream, MetadataStream.Size);

// Copies the sheets content to the Response
FileContent.Position := 0;
Response.CopyFrom(FileContent, FileContent.Size);

// Adds the final portion 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’);

// Builds the URL of the file in 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 header
IdHTTP.Request.CustomHeaders.Values[‘Authorization’] := 'Bearer ’ + pAccessToken;
IdHTTP.Request.ContentType := ‘application/json’;

// URL of the API in Google Apps Script
URL := ‘https://script.googleapis.com/v1/projects’;

// Body of the request in JSON
JSONPayload := ‘{“title”: “Meu Script OnEdit 12”, “parentId”: "’ + pIdDaPlanilha + ‘"}’;

// Creates the body of the request with UTF-8 manually
RequestBody := TStringStream.Create(UTF8Encode(JSONPayload)); // Codifica como UTF-8
Response := TStringStream.Create(‘’);

try
// Send 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 to 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;