Using Google App Scripts to append data to a sheet using MetaData to make sure it goes in the right column

Question

I want to pass an object to a Google Sheet Web App that I have written and have that data appended to the Google Sheet. I want to make sure the data ends up in the correct columns.

I can append the data to the file, but this could cause issues if columns are added/maniputated etc.

I have created column metadata for each column that corresponds to the object key.

I can read through the column metadata and find what column number each one represents. ie. if I get the metadata for "orderNumber" i can see it is in row 1.

Code for web app.

function doGet(e) {
  var p = e.parameter;
  var sheetName = "Orders";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var appendList = JSON.parse(p.appendList)[0];

  var returnValue = {};
  returnValue["status"] ="error";
  //var appendRow = sheet.getLastRow()+1;
  var values = []
  for (var key in appendList) {
    values.push(appendList[key]);
    Logger.log(searchAndReturnCol(key+"Column")); // just to show I can get the column number from meta data
  }
  sheet.appendRow(values);
  return ContentService.createTextOutput(JSON.stringify(returnValue));
}

function testDoGet() { // emmulates what will past over by the app
  var e = [];
  var test = [{
    'orderNumber' : "vsdv",
    'name' : "Bob",
    'porkDumpling' : 0,
    'prawnDumpling' : 0,
    'vegetarianDumpling' : 0,
    'sauce' : "Spicey",
    'paymentMethod' : "Cash",
    'dollarReceivable' : 5,
    'dollarReceived' :5,
    'collectionDate' : 44234244,
    'packed' : "No",
    'collected' : "No",
    'comments' : "This is a comment"
  }]

  var mod = JSON.stringify (test)
  e.parameter = {
    'appendList':mod,
    }

  doGet(e)
  //displayLog (doGet(e));
}

Code to find metadata

function searchAndReturnCol (key){
  var colLevel = cSAM.SAM.searchByKey (SSID , key);
  return colLevel.matchedDeveloperMetadata[0].developerMetadata.location.dimensionRange.endIndex
}

What I am unsure about is how to bring the two ideas together. I want to check the key in the object and then make sure that this data is inserted into the correct column based on the column metadata.

Answer 1

• In your Spreadsheet (the sheet of Orders), each column has the developer metadata.
  • Each key of developer metadata is the same with the keys of object you want to put to Spreadsheet.
• You want to put the values to the column, when the keys both the developer metadata and the data you give are the same.
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

• When doGet is run, the developer metadata is retrieved in order of the column. At that time, using the key of retrieved developer metadata, the data for putting to Spreadsheet is created from the giving data.

Modified script:

When your script is modified, please modify as follows.

From:

var values = []
for (var key in appendList) {
  values.push(appendList[key]);
  Logger.log(searchAndReturnCol(key+"Column")); // just to show I can get the column number from meta data
}

To:

var columnToLetter = function(column) { // From https://stackoverflow.com/a/21231012/7108653
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
};
var col = sheet.getLastColumn();
var values = [];
for (var i = 0; i < col; i++) {
  var d = sheet.getRange(columnToLetter(i + 1) + ":" + columnToLetter(i + 1)).getDeveloperMetadata();
  for (var j = 0; j < d.length; j++) {
    values.push(appendList[d[j].getKey()]);
  }
}

Note:

• If above modified script didn't retrieve the developer metadata from your Spreadsheet, please add the developer metadata to each column using the following script. If you want to rearrange the keys for columns, please modify keys.

  function createDeveloperMetadata() {
    var columnToLetter = function(column) { // From https://stackoverflow.com/a/21231012/7108653
      var temp, letter = '';
      while (column > 0) {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
      }
      return letter;
    };
    var keys = {orderNumber:"",name:"",porkDumpling:"",prawnDumpling:"",vegetarianDumpling:"",sauce:"",paymentMethod:"",dollarReceivable:"",dollarReceived:"",collectionDate:"",packed:"",collected:"",comments:""};
    var sheet = SpreadsheetApp.getActiveSheet();
    Object.keys(keys).forEach(function(e, i) {
      sheet.getRange(columnToLetter(i + 1) + ":" + columnToLetter(i + 1)).addDeveloperMetadata(e, keys[e]);
    });
  }
  

• When you add the developer metadata, please check the current metadata. Because the same keys can be added to the metadata.

  • If you want to update all metadata, I recommend to remove them and add new metadata.

• When you modified your script of Web Apps, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. Please be careful this. In your script, when you test the script with testDoGet, it is not required to redeploy it.

Reference:

• Class DeveloperMetadata

If I misunderstood your question and this was not the direction you want, I apologize.