Google App Script - Write to a specific Spreadsheet with HTML

Luca Crétier

unread,

May 6, 2022, 10:56:45 AM5/6/22

to Google Apps Script Community

Hi,

I have find and modified a code for Google App Script. Now I can enter 6 numbers in a web page, I can obtain sum but I'm not able to write in a Spreadsheet with a specific ID.

I attach code (sorry, but I don't retrieve the original web page) that generate a web page and the code that does not work.

Can you help me?

Best regards

Luca

---

CODE.GS

CODE WRITE.GS

INDEX.HTML

LINK 1.HTML

---

CODE.GS

// original code ???

// Aggiornamento LC 06/05/2022

function doGet(e) {

if (!e.parameter.page)

{

var htmlOutput = HtmlService.createTemplateFromFile('Index');

htmlOutput.message = '';

return htmlOutput.evaluate();

}

else if(e.parameter['page'] == 'Link 1')

{

Logger.log(JSON.stringify(e));

var htmlOutput = HtmlService.createTemplateFromFile('Link 1');

htmlOutput.firstname1 = e.parameter['firstname1'];

htmlOutput.firstname2 = e.parameter['firstname2'];

htmlOutput.firstname3 = e.parameter['firstname3'];

htmlOutput.firstname4 = e.parameter['firstname4'];

htmlOutput.firstname5 = e.parameter['firstname5'];

htmlOutput.firstname6 = e.parameter['firstname6'];

return htmlOutput.evaluate();

}

else if(e.parameter['page'] == 'Index')

{

var htmlOutput = HtmlService.createTemplateFromFile('Index');

htmlOutput.message = e.parameter['message'];

return htmlOutput.evaluate();

}

function getUrl() {

var url = ScriptApp.getService().getUrl();

return url;

}

CODE WRITE.GS

// original code ???

// Aggiornamento LC 06/05/2022

function CopyDataToNewFile() {

var sss = SpreadsheetApp.openById('ID'); // sss = source spreadsheet

var ss = sss.getSheetByName('Foglio1'); // ss = source sheet

var cell = sss.getRange("A1");

cell.setValue(z);

}

INDEX.HTML

// original code ???

// Aggiornamento LC 06/05/2022

<!DOCTYPE html>

<html>

<head>

function Link1()

{

var firstname1 = document.getElementById("firstname1").value;

var firstname2 = document.getElementById("firstname2").value;

var firstname3 = document.getElementById("firstname3").value;

var firstname4 = document.getElementById("firstname4").value;

var firstname5 = document.getElementById("firstname5").value;

var firstname6 = document.getElementById("firstname6").value;

var url = document.getElementById("url").value;

var link = document.createElement('a');

// Il link seguente non funziona (non compila correttamente la pagina di cui al link 1)

link.href = url+"?firstname1="+firstname1+"?firstname2="+firstname2+"?firstname3="+firstname3+"?firstname4="+firstname4+"?firstname5="+firstname5+"?firstname6="+firstname6+"&page=Link 1";

link.id = 'linkURL';

document.body.appendChild(link);

document.getElementById("linkURL").click();

}

function sum()

{

var w1 = document.getElementById('firstname1').value || 0;

var w2 = document.getElementById('firstname2').value || 0;

var w3 = document.getElementById('firstname3').value || 0;

var w4 = document.getElementById('firstname4').value || 0;

var w5 = document.getElementById('firstname5').value || 0;

var w6 = document.getElementById('firstname6').value || 0;

var z = parseInt(w1) + parseInt(w2) + parseInt(w3) + parseInt(w4) + parseInt(w5) + parseInt(w6);

document.getElementById('final').value=z;

};

</script>

</head>

<body>

<label>Misura 1</label><br>

<label>Misura 2</label><br>

<label>Misura 3</label><br>

<label>Misura 4</label><br>

<label>Misura 5</label><br>

<label>Misura 6</label><br>

<label>Somma</label><br>

<?var url = getUrl();?><input type="hidden" value="<?= url ?>" id="url" />

</body>

</html>

LINK 1.HTML (NOT NECESSARY)

// original code ???

// Aggiornamento LC 06/05/2022

<!DOCTYPE html>

<html>

<head>

function Back()

{

var url = document.getElementById("url").value;

var link = document.createElement('a');

link.href = url+"?page=Index&message=Back From Link 1";

link.id = 'linkURL';

document.body.appendChild(link);

document.getElementById("linkURL").click();

}

</script>

</head>

<body>

<span>Misura 1: <?= firstname1 ?></span><br>

<span>Misura 2: <?= firstname2 ?></span><br>

<span>Misura 3: <?= firstname3 ?></span><br>

<span>Misura 4: <?= firstname4 ?></span><br>

<span>Misura 5: <?= firstname5 ?></span><br>

<span>Misura 6: <?= firstname6 ?></span><br>

<?var url = getUrl();?><input type="hidden" value="<?= url ?>" id="url" />

</body>

</html>

Luca Crétier

unread,

May 6, 2022, 10:57:30 AM5/6/22

to Google Apps Script Community

P.S. Obviously

var sss = SpreadsheetApp.openById('ID'); // sss = source spreadsheet

"ID" is the ID of the Spreadsheet

Clark Lind

unread,

May 6, 2022, 2:37:58 PM5/6/22

to Google Apps Script Community

Try simply adding the url as part of the template:

function doGet(e) {
if (!e.parameter.page)
{
var htmlOutput = HtmlService.createTemplateFromFile('Index');
htmlOutput.message = '';

htmlOutput.url = getUrl();
return htmlOutput.evaluate();
}

....

Then you can use it on the html page:

CBMServices Web

unread,

May 6, 2022, 2:44:00 PM5/6/22

to google-apps-sc...@googlegroups.com

That would not work unless he gives everyone write access to the spreadsheet.

You should be able to call a function which opens the spreadsheet and write to it. Only thing to be careful is permissions. The Webapp will have to execute as yourself if you are only person who has edit access to the spreadsheet.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/715896a9-5ea0-45fa-86c4-f09931b47b29n%40googlegroups.com.

Luca Crétier

unread,

May 11, 2022, 3:05:14 PM5/11/22

to Google Apps Script Community

Hi,

thank you for your help. I will try.

Ciao :)

Luca

Reply all

Reply to author

Forward

Message has been deleted