GoogleSheets CMS

I am trying to implement a basic CMS via GoogleSheets, so I can edit text directly on the webpage and the edits are sent to the corresponding cell in GoogleSheets… and the webpage gets data from the GoogleSheet, completing the loop.
In principle this seems to work, but instead of overwriting the text in a corresponding cell, the text is appended, creating a new row. However if I edit the cell within the GoogleSheet and refresh my webpage, the input text field values are populated in the correct order.

So here is what I have so far… Fetching data (thanks @MaxZieb)

var xmlhttp = new XMLHttpRequest();
var url = "";

xmlhttp.onreadystatechange = function() {
	if (this.readyState == 4 && this.status == 200) {
		var data = JSON.parse(this.responseText);
		hypeDocument.customData = data;
};"GET", url, true);

adding the data to the value of the text input fields

  var data = hypeDocument.customData;
  for (i = 0; i < data.feed.entry.length; i++) { 
  var Input = document.getElementsByClassName('input')[i];

  var Title = data.feed.entry[i].gsx$title.$t

  Input.value = Title;


So far so good, works as expected… Now I can send the values I type into the input fields

var PlaceHolders = document.getElementsByClassName('PlaceHolder');

  for (i = 0; i < PlaceHolders.length; i++) { 
  Input = document.getElementsByClassName('input')[i];
	var xmlhttp = new XMLHttpRequest();
	var url = "";"GET", url+"?title="+Input.value , true);

The result is sent and is appended to the sheet but the order seems random, not sequential. Also I need to overwrite the values. I’m using this google script, but can’t work it out. Any pointers would be amazing. Thank you.

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";
//  2. Run > setup
//  3. Publish > Deploy as web app 
//    - enter Project Version name and click 'Save New Version' 
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) 
//  4. Copy the 'Current web app URL' and post this in your form/script action 
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
function doPost(e){
  return handleResponse(e);
function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1]
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  /* // If you are passing JSON in the body of the request uncomment this block
  var jsonString = e.postData.getDataAsString();
  e.parameter = JSON.parse(jsonString);
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
  } finally { //release lock
function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
1 Like

Can you share a Hype document as well?

Sorry, was cut-and-pasting from it so forgot to add it. (64.0 KB)

If I remember correctly, in the google sheet AppScript. When you get the sheet you need to do a sort order Each time so it is always the same order.

example from my own code.

function addLatLongToSheet( theArray ){
    var sheet = SpreadsheetApp.openById('xxxxxxxxxxxxxxxxxx')
      var sheetGet = sheet.getSheetByName("newLats");
 sheetGet.sort(1, true);
// Logger.log(theArray)
   for (i = 0; i < theArray.length; i++) { 
     var theIndex = theArray[i].index 
     var lat_ = theArray[i]
     var  long_ =   theArray[i].latlong.long
     var quid_ =  theArray[i].unqid
     sheetGet.getRange('G' + theIndex ).setValue(lat_);
     sheetGet.getRange('H' + theIndex ).setValue(long_);
      sheetGet.getRange('I' + theIndex ).setValue(theIndex);
 //sheetGet.getRange('J' + theIndex ).setValue(quid_);


You probably can find more details in the AppScript Docs.

1 Like

Thanks @MarkHunte. I will play around with that code and report back. I am still unclear as to how the Google Scripts are even applied to the spreadsheet as it seems to not update, even when I make changes to it. Have to save the script. Deploy it as web app. Commit changes etc… Testing small changes takes a long time.

Happy New Year all!
I have been playing around with Google Sheets as CMS with some mixed results. Editing from Google Sheets, seems to work very well and the changes are reflected perfectly. However, I can’t seem to make the editing work the other way, so that changing the text on the site will change the relevant row in the google sheet. The text is brought in and split into letters and then each letter is populated in a new cell. See video below.

This is the new Google App Script:

// Google script that responds to GET requests and adds the passed-in
// parameters (form field values in our case) to a spreadsheet.

function doGet(e){
  const ssapp = SpreadsheetApp.openByUrl("");
  const sheet = ssapp.getSheetByName("Sheet1");

  return insert_value(e,sheet);

function insert_value(request,sheet){

  var Titles = request.parameter.titles;

  for (var i = 2; i < Titles.length; i++) {
  sheet.getRange(i, 1, 1, 1).setValue(Titles[i]);

And this is the way I’m sending the data from Hype:

var PlaceHolders = document.getElementsByClassName('PlaceHolder');

  for (i = 0; i < PlaceHolders.length; i++) {

  var Input = document.getElementsByClassName('input')[i];
	var xmlhttp = new XMLHttpRequest();
	var url = "";"GET", url+"?titles="+Input.value, true);

If anyone knows how to handle / split / combine arrays… I’m guessing it has something to do with that.

Happy New Year!

Can you upload the new doc either here or PM me. I cantry and see whats going on.


So I set my own one up.

This is not the be all and end all but should give you a better idea of what you can do.
Ideally the code could deal with the A1 (titles ) and index counting better. But I have put in a quick and dirty if -> return in there to stop overflowing…

So what you really should be doing in the hype send function is to get all the inputs files at once and put them in an array.

then place the array in a json object and send the json as a string in the send query.

var jarray = []
  var inputs = document.getElementsByClassName('input'); // get all input elements
  for (i = 0; i < inputs.length; i++) {
// - iterate over each item in the inputs

jarray.push(inputs[i].value)    // - Add  input value to jarray array 
 		// - create new assosiated array/ json object with the key (name) tittles and the value as the jarray array
 		 var obj = {
           "titles" : jarray
   var  newLoad = JSON.stringify(obj) // - convert the json to a string so it is able to be sent via the query below.
	var xmlhttp = new XMLHttpRequest();
	var url = "";"GET", url+"?titles="+ newLoad, true);

Then in the App-script,
Parse the json string back into an object and process it.

    function insert_value(response,sheet){

    var data = JSON.parse(response.parameter.titles );
      var lastIndex = sheet.getLastRow()// - count of rows
      var array = data["titles"] // - object name in json is titles and the value  will be the array
      for (i = 0; i < array.length; i++) { 
     // - iterate over each item in the array 
        if (i+1 >= lastIndex){
  // we do not want to add any rows past the actual number of rows.
 sheet.getRange('A' + (i+2) ).setValue(data["titles"][i]);
         /*   we want to start writing from row 2  so we +2 on the i index count. i.e 0 + 2 = A2, 1+ 2 = A3.. 
This allows us to keep the correct indexing for the **i**  var  to use against **data["titles"][i]** values being added.

 // sheet.getRange('B1' ).setValue( lastIndex); //-- test so we can see some othe feed back


This all works in my version.

And like I say not the be all and end nor pretty, but should move you forward.


Hi @MarkHunte. Thanks so much for looking into this. Already a highlight of 2020! I tested this and it works really well. I will study the code so I can understand it better and post back with further developments.



Just added some comments above…

1 Like

Look forward to it. I know very little about these types of operations - time to change that in 2020!

1 Like

Cool, I do like using the App Script stuff, it has opened some tasks up for me at work.
The one thing I do find though is its IDE is not the most easiest to use. Due to it being web based only.

Also I use it slightly different to how for cms to @gleb is using it here. Where I build my pages dynamically at run time using the App Script HTML API. Also I keep all of it in house and using the normal google permissions to limit who can run the scripts. This save me a ton of work in writing and figuring out security. So even though I can publish the scripts for anyone within my domain only those who have permissions to shared files it uses in G-drive can actually run it.

Gleb 'sway of doing in the above afaik publishes to the world.

Hope that makes sense.

1 Like

I’m new in Hype and just tried the script and macro, but I still get the error:
TypeError: Eigenschaft “parameter” von undefined kann nicht gelesen werden. (Zeile 4, Datei “Code”) for the macro. Can anyone help me? - why is response not defined in my macro?

I can write from the Sheet on the file, but not from the browser onto the sheet.

Thanks a lot in advance, Sol

Hi @Solvejg

I’m not sure exactly what’s going on. But I found that you always have to create a new version of the Google Script App and then click update. So in the script editor window click “publish”, then “Deploy as web app”, then “project version” dropdown to “new” (I’m on version 227!!!), then “update”, then in the new pop-up “OK”. I had to do this every time I made any changes in the google script in order for it to take any effect. (At least this is how it works for me.)

1 Like

I have been playing around with this now and was quite happy how it was progressing, until I hosted it remotely. The changes are being sent to google sheet from the local copy of the site (preview from Hype), but not from I’m guessing because it’s not secure? I hope this is something I can overcome.

On the plus side, when pressing the EDIT button in the top-right corner, I can now swap images (using Getty Images API) and change text. Next I will look into formatting text either using Scribe or something similar. If anyone has any ideas how to integrate it, that would be useful. Also my code essentially replicates / duplicates the arrays that @MarkHunte set up above. I’m wondering if the code can be streamlined.

One of the issues I will try to solve is that at the moment I’m relying to have an equal number of images, titles, subtitles and text boxes, otherwise the code doesn’t work.

New code for sending data to GoogleSheet:

var TitleArray = []
var SubtitleArray = []
var TextAreaArray = []
var ImageNumberArray = []
var ImageTypeArray = []
  var Titles = document.getElementsByClassName('title');
  var Subtitles = document.getElementsByClassName('subtitle');
  var TextAreas = document.getElementsByClassName('TextArea');
  var ImageNumbers = document.getElementsByClassName('ImageNumber');
  var ImageTypes = document.getElementsByClassName('ImageType');
  for (i = 0; i < Titles.length; i++) {

  for (i = 0; i < Subtitles.length; i++) {

  for (i = 0; i < TextAreas.length; i++) {

  for (i = 0; i < ImageNumbers.length; i++) {

  for (i = 0; i < ImageTypes.length; i++) {
 		   var TitleObj = {
           "titles" : TitleArray
           var SubtitleObj = {
           "subtitles" : SubtitleArray
           var TextAreaObj = {
           "textareas" : TextAreaArray
           var ImageNumberObj = {
           "imagenumbers" : ImageNumberArray
           var ImageTypeObj = {
           "imagetypes" : ImageTypeArray
     var  TitlesLoad = JSON.stringify(TitleObj)
     var  SubtitleLoad = JSON.stringify(SubtitleObj)
     var  TextAreaLoad = JSON.stringify(TextAreaObj)
     var  ImageNumberLoad = JSON.stringify(ImageNumberObj)
     var  ImageTypeLoad = JSON.stringify(ImageTypeObj)
	var xmlhttp = new XMLHttpRequest();
	var url = "";"GET", url+"?titles="+ TitlesLoad + "&subtitles=" + SubtitleLoad + "&textareas=" + TextAreaLoad + "&imagenumbers=" + ImageNumberLoad + "&imagetypes=" + ImageTypeLoad, true);

New code for getting data and embedding images:

$.getJSON("", function(data) { 
var apiKey = 'xxxxxxxx';

var MyURL = "";

for (var i = 0; i < data.feed.entry.length; i++) {

//fields and google sheet value pairs

  var Title = document.getElementsByClassName('title')[i];  
  var Titles = data.feed.entry[i].gsx$titles.$t
  var Subtitle = document.getElementsByClassName('subtitle')[i];
  var Subtitles = data.feed.entry[i].gsx$subtitles.$t
  var TextArea = document.getElementsByClassName('TextArea')[i];
  var TextAreas = data.feed.entry[i].gsx$textareas.$t
  var ImageType = document.getElementsByClassName('ImageType')[i];  
  var ImageTypes = data.feed.entry[i].gsx$imagetypes.$t;
  var ImageNumber = document.getElementsByClassName('ImageNumber')[i];
  var ImageNumbers = data.feed.entry[i].gsx$imagenumbers.$t
//clickable and editable fields being set

  Title.value = Titles;
  Subtitle.value = Subtitles;
  TextArea.innerHTML = TextAreas;
  ImageType.innerHTML = ImageTypes;
  ImageNumber.value = ImageNumbers; 
//Actual image and credits used for Getty Images API  

  var ImageCredit = document.getElementsByClassName('ImageCredit')[i];
  var Image = document.getElementsByClassName('image')[i];

  loadData(ImageTypes, ImageCredit, Image, data, i);

function loadData(ImageTypes, ImageCredit, Image, data, i){

    if (ImageTypes == "ED") {

        MyURL = "" + data.feed.entry[i].gsx$imagenumbers.$t + "&fields=high_res_comp,title,artist,collection_name,links";

                type: 'GET',
                url: MyURL,

                beforeSend: function (request) {
                request.setRequestHeader("Api-Key", apiKey)

            .done(function (data) {

                Image.innerHTML = '<div style="width: 100%; height: 100%; position: absolute; background-position: center center !important; background-image: url(' + data.images[0].display_sizes[0].uri + '); overflow: hidden; background-size: cover !important; -webkit-background-size: 100%; display: inline; z-index: 1; opacity: 1; background-repeat: no-repeat no-repeat;"></div>';
                ImageCredit.innerHTML = "<a href='" + data.images[0].id + "?editorialproducts' target='_blank'>"+ data.images[0].id + ", " + data.images[0].artist + " / " + data.images[0].collection_name + " / Editorial </a>"; 

    } else {
        MyURL = "" + data.feed.entry[i].gsx$imagenumbers.$t + "&fields=high_res_comp,title,artist,collection_name";
                type: 'GET',
                url: MyURL,
                beforeSend: function (request) {
                request.setRequestHeader("Api-Key", apiKey)

            .done(function (data) {
                Image.innerHTML = '<div style="width: 100%; height: 100%; position: absolute; background-position: center center !important; background-image: url(' + data.images[0].display_sizes[0].uri + '); overflow: hidden; background-size: cover !important; -webkit-background-size: 100%; display: inline; z-index: 1; opacity: 1; background-repeat: no-repeat no-repeat;"></div>';
                ImageCredit.innerHTML = "<a href='" + data.images[0].id + "?license=rf' target='_blank'>"+ data.images[0].id + ", " + data.images[0].artist + " / Creative RF </a>"; 



Thank you!

Some interesting behaviour (at least for me, learning about these things).
I have updated the ssl certificate, so the page can now be accessed at
However Chrome asks to load unsecure scripts and even after loading it still doesn’t send data when editing text fields, turns red to “unsecure”.
Safari, just loads blank page with placeholder text and empty boxes. Same for Firefox.

If I export the project from hype, the local CMS_Test.html does not work in Chrome or Safari or Firefox. Same behaviour as the online file.

However if I click preview from within Hype, the output behaves exactly as expected across all three browsers. Have no idea why and what a workaround could be.

I have stripped out the images api so the attached file only works with text boxes. (51.3 KB)

Thanks for any ideas or workarounds.

Change your jquery link to https

Thanks @MarkHunte

Changed to:

<script type="text/javascript" src=""></script>

In the Head HTML, but still only works in the preview.

I also think there is a setup diference in the two spreadsheets you are using. The one in preview works but if you replace it with the one on the site it does not.

Also you should try and add some error or response handling when the code first runs so you are not getting that initial error related to the titles

Also not fully tested this snippet and thought out while I was just quickly looking at what was going on…

var EditToggle = document.getElementById('EditToggle');
var PlaceHolders = document.getElementsByClassName('PlaceHolder');

var classElements = ['ImageEditor','title','subtitle','ImageNumber','TextArea']
for (var i = 0; i < classElements.length; i++) {
  var  theClass = classElements[i].className
  switch(theClass) {
    case "Titles","Subtitles","ImageNumbers":
      classElements[i].readOnly = true;
      classElements[i].style.background = "none"; 
    case "TextAreas":
    classElements[i].contentEditable = false;
     classElements[i].style.background = "none";
      case "ImageEditors":
   classElements[i].style.display = "none";
  EditToggle.innerHTML = "EDIT";