Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pushing data from an object to a spreadsheet in google scripts

I have grabbed data from my source spreadsheet and placed it into an array of objects that contain name : value pairs for each row in the spreadsheet.

The name comes from the header row in my spreadsheet, and the values come from each following row.

I am then looping through the name and value pairs in the objects and modifying them before they go into the target spreadsheet.

This is what the data looks like in my debugger:

objectData[29] | Array | [{id:1001, name:"John", cats:"3"}, {id:1002, name:"Shelley", cats:"9"}...]

I would like to take the data in this object and place it in the target spreadsheet with the condition that the object names match the column names in the spreadsheet, and the object values are placed on a new row underneath the matching columns.

I'm really new to scripting and am having a horrendous time figuring out how to do this.

like image 704
WC123 Avatar asked Mar 31 '17 00:03

WC123


People also ask

Can Google Sheets pull data from an API?

Once you've set up your API to Google Sheets connection, click Save And Run to get data to your spreadsheet.

How do I send data to Google script?

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.


1 Answers

This and much more available at https://gist.github.com/mhawksey/1442370

// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }
  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}

Edit: I noticed a function used above was not included

// Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    //if (!isAlnum(letter)) {
    //  continue;
    //}
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}

the link at the top of answer includes code for procedures that you have already accomplished. Seeing how others attain the same goal is often very enlightening.

like image 127
ScampMichael Avatar answered Sep 29 '22 11:09

ScampMichael