��权限没有,则隐藏 function forum_list_access_filter($forumlist, $gid, $allow = 'allowread') { global $grouplist; if (empty($forumlist)) return array(); if (1 == $gid) return $forumlist; $forumlist_filter = $forumlist; $group = $grouplist[$gid]; foreach ($forumlist_filter as $fid => $forum) { if (empty($forum['accesson']) && empty($group[$allow]) || !empty($forum['accesson']) && empty($forum['accesslist'][$gid][$allow])) { unset($forumlist_filter[$fid]); } unset($forumlist_filter[$fid]['accesslist']); } return $forumlist_filter; } function forum_filter_moduid($moduids) { $moduids = trim($moduids); if (empty($moduids)) return ''; $arr = explode(',', $moduids); $r = array(); foreach ($arr as $_uid) { $_uid = intval($_uid); $_user = user_read($_uid); if (empty($_user)) continue; if ($_user['gid'] > 4) continue; $r[] = $_uid; } return implode(',', $r); } function forum_safe_info($forum) { //unset($forum['moduids']); return $forum; } function forum_filter($forumlist) { foreach ($forumlist as &$val) { unset($val['brief'], $val['announcement'], $val['seo_title'], $val['seo_keywords'], $val['create_date_fmt'], $val['icon_url'], $val['modlist']); } return $forumlist; } function forum_format_url($forum) { global $conf; if (0 == $forum['category']) { // 列表URL $url = url('list-' . $forum['fid'], '', FALSE); } elseif (1 == $forum['category']) { // 频道 $url = url('category-' . $forum['fid'], '', FALSE); } elseif (2 == $forum['category']) { // 单页 $url = url('read-' . trim($forum['brief']), '', FALSE); } if ($conf['url_rewrite_on'] > 1 && $forum['well_alias']) { if (0 == $forum['category'] || 1 == $forum['category']) { $url = url($forum['well_alias'], '', FALSE); } elseif (2 == $forum['category']) { // 单页 $url = ($forum['threads'] && $forum['brief']) ? url($forum['well_alias'] . '-' . trim($forum['brief']), '', FALSE) : url($forum['well_alias'], '', FALSE); } } return $url; } function well_forum_alias() { $forumlist = forum_list_cache(); if (empty($forumlist)) return ''; $key = 'forum-alias'; static $cache = array(); if (isset($cache[$key])) return $cache[$key]; $cache[$key] = array(); foreach ($forumlist as $val) { if ($val['well_alias']) $cache[$key][$val['fid']] = $val['well_alias']; } return array_flip($cache[$key]); } function well_forum_alias_cache() { global $conf; $key = 'forum-alias-cache'; static $cache = array(); // 用静态变量只能在当前 request 生命周期缓存,跨进程需要再加一层缓存:redis/memcached/xcache/apc if (isset($cache[$key])) return $cache[$key]; if ('mysql' == $conf['cache']['type']) { $arr = well_forum_alias(); } else { $arr = cache_get($key); if (NULL === $arr) { $arr = well_forum_alias(); !empty($arr) AND cache_set($key, $arr); } } $cache[$key] = empty($arr) ? '' : $arr; return $cache[$key]; } ?>google sheets - Label the row according to the source - Stack Overflow
最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

google sheets - Label the row according to the source - Stack Overflow

programmeradmin2浏览0评论

I want to importrange from Spreadsheet OLD with several tabs/sheets into Spreadsheet NEW. What i want to do is to import values from SheetA and Sheet B. First column: vstack(importrange(SheetA!A5:A),importrange(SheetB!B5:B) easy The problem is here. In second column, I would like to do something like if the row imported is from sheet A , then "Sheet A", if the row imported is from sheet B, then "Sheet B".

My failed approaches: 1.

=VSTACK(
  ARRAYFORMULA(IF(IMPORTRANGE("SheetA", "SheetA!B5:B") <> "", "Sheet A", "")),
  ARRAYFORMULA(IF(IMPORTRANGE("SheetB", "SheetB!B5:B") <> "", "Sheet B", ""))
)

this doesn't work, because i assume, arrayformula doesn't work with vstack. Sheet A is there but it did not anything after that. but you can prove me wrong. 2.

=ARRAYFORMULA(
  IF(A2:A<>"", 
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("SheetA", "SheetA!B5:B"), 0)), "Sheet A",
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("Sheet B", "SheetB!B5:B"), 0)), "Sheet B"
  ""))

close enough, but some of B5:B are from two sheets A or B. So, it would only take SheetA even though it is from SheetB.

So, please give your suggestion.

I want to importrange from Spreadsheet OLD with several tabs/sheets into Spreadsheet NEW. What i want to do is to import values from SheetA and Sheet B. First column: vstack(importrange(SheetA!A5:A),importrange(SheetB!B5:B) easy The problem is here. In second column, I would like to do something like if the row imported is from sheet A , then "Sheet A", if the row imported is from sheet B, then "Sheet B".

My failed approaches: 1.

=VSTACK(
  ARRAYFORMULA(IF(IMPORTRANGE("SheetA", "SheetA!B5:B") <> "", "Sheet A", "")),
  ARRAYFORMULA(IF(IMPORTRANGE("SheetB", "SheetB!B5:B") <> "", "Sheet B", ""))
)

this doesn't work, because i assume, arrayformula doesn't work with vstack. Sheet A is there but it did not anything after that. but you can prove me wrong. 2.

=ARRAYFORMULA(
  IF(A2:A<>"", 
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("SheetA", "SheetA!B5:B"), 0)), "Sheet A",
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("Sheet B", "SheetB!B5:B"), 0)), "Sheet B"
  ""))

close enough, but some of B5:B are from two sheets A or B. So, it would only take SheetA even though it is from SheetB.

So, please give your suggestion.

Share Improve this question edited Jan 14 at 3:57 z.. 13.2k2 gold badges9 silver badges20 bronze badges asked Jan 14 at 3:01 cywhcywh 92 bronze badges 3
  • Welcome to StackOverFlow! Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link ) or an anonymous sample spreadsheet (using this link – Alma_Matters Commented Jan 14 at 3:06
  • So, there may data in either SheetA or SheetB? Better to share few sample data showing your desired output? – Harun24hr Commented Jan 14 at 3:06
  • oh sorry! old spreadsheet - docs.google/spreadsheets/d/… new spreadsheet - docs.google/spreadsheets/d/… – cywh Commented Jan 14 at 3:18
Add a comment  | 

2 Answers 2

Reset to default 1

You can try this formula using Query:

=QUERY(
  {
    IMPORTRANGE("OLD_SHEET_URL", "SheetA!B2:B"),
    ARRAYFORMULA(IF(IMPORTRANGE("OLD_SHEET_URL", "SheetA!B2:B")<>"", "SheetA", ""))
  ;
    IMPORTRANGE("OLD_SHEET_URL", "SheetB!B2:B"),
    ARRAYFORMULA(IF(IMPORTRANGE("OLD_SHEET_URL", "SheetB!B2:B")<>"", "SheetB", ""))
  },
  "SELECT Col1, Col2 WHERE Col1 IS NOT NULL",
  0
)

Note: Kindly replace OldsheetUrl with the link to your old sheet.

Sample Output:

Stack of food Source of Sheet
Carrot SheetA
Apple SheetA
Cake SheetA
Pineapple SheetA
Grape SheetA
Pear SheetA
Chicken SheetA
Jumbo SheetB
Cake SheetB
Pie SheetB
Grape SheetB
Strawberry SheetB

You can use REDUCE.

=ARRAYFORMULA(
   REDUCE(
     {"Food", "Sheet"},
     {"SheetA", "SheetB"},
     LAMBDA(result, sheet, LET(
       import, TOCOL(IMPORTRANGE(
         "Old_Spreadsheet_ID",
         sheet & "!B2:B"
       ), 1),
       stack, HSTACK(import, IF(N(import)^0, sheet)),
       VSTACK(result, IF(ISERROR(stack), TOCOL(,1), stack))
     ))
   )
 )
发布评论

评论列表(0)

  1. 暂无评论