-1

Not sure if this is even possible, but would anyone know if theres a way of using app script to move cell data to a specified area. Sort of a reverse Vlookup? I want it to send a cell's data to an area that matched the cell's title, in a similar way to how Vlookup will pull data from a cell that matches the title... But in reverse.

Thanks for any help you guys could give :)

Update: Below is a link to a practice cell with no script https://docs.google.com/spreadsheets/d/1ie8k6a9gcOg06tVVc1bCFhandIH7vy2LUgk2_vusOAc/edit?usp=sharing

The idea is that The cell containing £300 (D7) would be directed to B7 through the titles of their cells (both being 02/01/22)

3
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Dec 31 '21 at 23:20
  • Hi and welcome to WebApps. Thanks for the spreadsheet. You refer to "Worksheets" and this isn't a term used with Google Sheets. So, just to clarify... do you have one spreadsheet file with two sheets, or two spreadsheet files?
    – Tedinoz
    Jan 1 at 6:32
  • Its two spreadsheet files. One file contains the source information, and the second is where this will be stored. Jan 1 at 13:08
1

It is unclear why you would need to use a script, because this can be done with a plain vanilla hlookup() formula, like this:

=arrayformula( iferror( hlookup(A6:A15, D6:F7, rows(D6:F7), false) ) )

See the new Solution sheet in your sample spreadsheet. To read the data from another spreadsheet file, use an importrange() in place of D6:F7 and 2 in place of rows(D6:F7).

To answer your question, you can read the data from another spreadsheet file with SpreadsheetApp.openById() and do the lookup with Array.indexOf() like this:

function transverseLookup() {
  const sourceRange = SpreadsheetApp
    .openById('1ie8k6a9gcOg06tVVc1bCFhandIH7vy2LUgk2_vusOAc')
    .getRange('Sheet1!D6:F7');
  const targetRange = SpreadsheetApp
    .getActive()
    .getRange('Sheet1!A6:B15');
  const sourceKeys = sourceRange
    .offset(0, 0, 1)
    .getDisplayValues().flat();
  const sourceValues = sourceRange
    .offset(1, 0, 1)
    .getValues().flat();
  const values = targetRange.getDisplayValues()
    .map(row => row[0])
    .map(key => [sourceValues[sourceKeys.indexOf(key)] ?? null]);
  targetRange.offset(0, 1, values.length, values[0].length)
    .setValues(values);
}
5
  • Hey, I know that it's possible using formulas, but I was wondering if it was possible in app script Jan 1 at 13:07
  • Edited the answer. Jan 1 at 15:11
  • The reason id like it to be a script is due to the original data thats being moved, is changing each week. I have a system that receives the data, but wanted a script to automatically filter this into a separate area based on where the data is from. Sounds complicated, but its sorta like getting a box of fruit, and needing it separated into whats apples and whats pears :) Jan 1 at 19:03
  • The hlookup() formula will do that automatically as the data gets updated. To do it with a script, set the transverseLookup() function to run on a time-driven trigger. Jan 1 at 20:35

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.