Using google spreadsheet values for a drop down in a Google Docs Side bar

Question

There doesnt seem to be much out there r.e. google docs side bars.

Other than the fantastic work done by Mogsdad.

His cursor position tool is 1/2 of what I need already (How to poll a Google Doc from an add-on).

I have a google sheets template with ~200 possible placeholders that need to be put into the google doc, populated, and then PDF'd.

What I cant seem to find anywhere is how to get ranges from a google sheet into a docs side bar. This way when the template is being built, I can set up some variable dynamic drop downs that would place the correct place holder at the cursor position.

Anyone able to point me in the right direction? (Yes I am aware of Autocrat :P)

Rubén Rubén · Accepted Answer · 2016-11-11T15:33:48

Use the Google Apps Script Spreadsheet service, first to open the spreadsheet then to get the range and lastly to get the values.

Example:

/**
 * Get the values from a range speficied by the Spreadsheet ID and the range reference or 
 * name
 *
 * @param {'1FkUd199CS3U25bfb5WxP-Jy--qcgD4NTHYWNMw8AtiA'} id Spreadsheet id
 * @param {'Sheet1!A1:A100'} stringReference Reference string
 * @return 2D array with the values of the specified range
 */
function getSpreadsheetValues(id,stringReference){
  var ss = SpreadsheetApp.openById(id);
  var range = ss.getRange(stringReference);
  return range.getValues();
}

Using google spreadsheet values for a drop down in a Google Docs Side bar

1 Answers