CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Luiz
Luiz

Reputation: 101

Fill cell based on another sheets value

I have a spreadsheet on Google sheets with 2 sheets, Sheet1 and Sheet2. On Sheet1 I have 2 columns (ID1 and Result1), in which are filled. On Sheet2 I have 2 columns (ID2 and Result2), in which ID2 is filled and Result2 is filled with the word "empty".

Sheet1 in my real spreadsheet is getting data from Google Forms, so every time Sheet1 receives a value on ID1 and Result1, it should search for the ID1 value on Sheet2's ID2 and when finding, paste Result1 value in Result2 in the row it's was found.

Result2 would then only be updated when there's new data on Sheet1 submitted from the form.

I created this editable form and spreadsheet to make it easier to understand (I also added a note explaining it in there). You can see the form here and the spreadsheet here.

Upvotes: 1

Views: 2956

Answers (3)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

After looking through the current answers and comments I think I understand what you are looking for.

Answer:

You can do this in Google Apps Script by creating a function that is bound to your Google Form which collects the latest response and does the data processing, and making it run on Form submission.

Steps to take:

Firstly, on your Form, you will need to create a bound script. From the menu in the top right of the Form edit page, click ⋮ > Script editor, which will open a new script page.

From there you can make a script which will automatically do this for you, and make an Installable Trigger which runs when you need.

Code:

After opening the Script Editor, you will see a ready-to-edit function that looks like this:

function myFunction() {

}

Replace the entire script with the following code:

function onSubmit(e) {
  var responses = FormApp.getActiveForm().getResponses();
  var response = responses[responses.length - 1].getItemResponses();
  var connectedSheet = SpreadsheetApp.openById('<your-sheet-id>').getSheets();
  var sheet2 = connectedSheet[1];  
  var result2Column = sheet2.getRange('A1:A').getValues();
  
  for (var i = 0; i < result2Column.length; i++) {
    if (response[1] == result2Column[0]) {
      sheet2.getRange('B' + (i +1)).setValue(response[0]);
    }
  }
}

Make sure to replace <your-sheet-id> with the unique ID of your Google Sheeet - you can find this in the URL of the sheet between the d/ and /edit like so:

https://docs.google.com/spreadsheets/d/<your-sheet-id>/edit

Run the script by pressing the play button (►) and authorise the application to run.

Then go to Edit -> Current Project's Triggers and set up a new installable trigger with the following settings:

  • Choose which function to run: onSubmit
  • Choose which deployment should run: Head
  • Select event source: From form
  • Select event type On form submit

Explanation:

This script will run each time a new form submission is made automatically - it will take the ID from the form response and search Sheet2 for it. If it's found, then the response given for result will be put in Sheet2 also, next to the corresponding ID.

References:

Upvotes: 0

player0
player0

Reputation: 1

do it simply like this:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Sheet1!A:B, 2, 0)))

0

0

or reset by checkbox:

0

Upvotes: 0

Terry W
Terry W

Reputation: 3257

In cell B2 of your second sheet I entered the following:

=VLOOKUP(A2,Sheet1!A:B,2,FALSE)

Results

I guess VLOOKUP on Google-Sheets does allow reference across worksheets?

FYI, when entering the formula, after enter the first argument which is your look up criteria, you can click your sheet1 and highlight column A and B, then go back to your sheet2 (with the second argument automatically filled by the system) to finish the formula with the third (result range column position within the look up range) and fourth argument (TRUE for approximate match and FALSE for exact match) and hit Enter to exit the formula.

Upvotes: 1

Related Questions