0

I have a google sheet with the following headers:

| name   |  prop_id |  number | street | city | state | zip | phone | first_name | last_name | email |
|--------|:--------:|--------:|-------:|-----:|------:|----:|------:|-----------:|-----------:------:|

Ultimately, I want to write a custom function that will will take each row in the sheet and make a Post call to an an API to post a payload.

I want to create a JSON object from each row with the headers as the keys and the corresponding cell as the value.

I have found many solutions/resources for exporting an entire sheets into JSON but I don't want to export, I want to turn each row into a JSON a separate object and use that object as the payload to an API call.

Here's what I've tried so far to get the values from a row of a Google Sheet:

// get current row
function getActiveRow() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeRow = sheet.getActiveCell().getRow();

  return activeRow;
}

// get data from row
function getDataRow() {  
  //Get the data to send from the spreadsheet and prep variables
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(getActiveRow(), 1, 1, 11);
  var row = dataRange.getValues();
  var headers = sheet.getDataRange().getValues()[0];
  var payload = {};

  return row   
}

When I try to apply this formula to any row after the first row (excluding headers) I get a #NAME? error.

Additionally, even with just working with the first row of data (after the headers) I'm trying to buildout the payload object like so:

payload[headers[0]] = row[0];
return payload;

but am not seeing anything return.

Also, what is the equivalent of console.logging when trying to debug with a google script?

  • 1
    To log something, use Logger.log("..."). You can see the log output in View -> Logs. – Vidar S. Ramdal Sep 1 '16 at 12:10
  • "When I try to apply this formula" ... What formula is that? Could you perhaps share a spreadsheet with us? It would be much easier to see what you're trying to do. – Vidar S. Ramdal Sep 1 '16 at 12:11
  • 1
    @VidarS.Ramdal: See my answer – Rubén Sep 1 '16 at 12:19
1

When I try to apply this formula to any row after the first row (excluding headers) I get a #NAME? error

The#NAME? error occurs on custom functions when the custom functions use a service, in this case SpreadsheetApp, that requires authorization to run and it is not authorized yet.

To authorize your script,

  1. Go to the script editor by clicking on Tools > Script editor
  2. In the toolbar, click the function selector and select one function
  3. Click the Run or Debug buttons.

See https://developers.google.com/apps-script/overview for further details.

Also, what is the equivalent of console.logging when trying to debug with a google script?

For custom logging use Logger.log(data). For further details, see https://developers.google.com/apps-script/troubleshooting

UPDATE:

Google added Stackdriving Logging support to Google Apps Script so now it's possible to use the Class console for logging messages.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.