CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Fudgeeman
Fudgeeman

Reputation: 1

How to reference a value in a different sheet?

This is prob simple but I'm new to this. I'm trying to map out my variables at the beginning of a Google Form>Spreadsheet>Template Doc workflow. The form fills out the following 4 columns (timestamp through campaign_start_date) but the other 2 columns (insertion_order_number and month) are values that are coming from a different sheet in the SAME google spreadsheet/workbook. How do I reference these values?

  //e.values is an array of form values
  var timestamp = e.values[0];
  var client = e.values[1];
  var advertiser = e.values[2];
  var total_spend_amount = e.values[3];
  var campaign_start_date = e.values[4];
  var insertion_order_number = 
  var month =

Upvotes: 0

Views: 80

Answers (1)

New_2_Code
New_2_Code

Reputation: 328

As mentioned in the above comment. You want to open a spreadsheet by it's ID. Once you have that spreadsheet you want to the range where "Campiagn Start Date" and Insertion Order Month" are in. Once you have that range, you want to get the value(s) inside that range. Then you can assign the value of those two variables to be the value that you got above.

The below code is an example of what you could do. I would highly advise using getValues() over getValue. But for the purpose of this sample code, will keep it nice and simple:

function onSubmit(e) {

    //Get Spreadsheet by ID and get sheet by name 
    var ss = SpreadsheetApp.openById("YOUR ID HERE");
    var sheet = ss.getSheetByName("YOUR SHEET NAME HERE");

    var campiagnStartDate = sheet.getRange(2, 2, 1, 1).getValue(); //Gets the value in B2
    var insertionOrderMonth = sheet.getRange(2, 3, 1, 1).getValue(); //Gets the value in B3

    var timestamp = e.values[0];
    var client = e.values[1];
    var advertiser = e.values[2];
    var total_spend_amount = e.values[3];
    var campaign_start_date = e.values[4];
    var insertion_order_number = campiagnStartDate
    var month = insertionOrderMonth
}

Upvotes: 1

Related Questions