0

Hello i'm using google sheet as my second databse and in the main page called Companies i have a list of companies as shown below: enter image description here

and i wrote a function that generate a new tab for every companie in the first column. here is what a tab looks like enter image description here

my goal is in the companies tab under "Workers" i want to get the value of "Total workers" of each companie. the list of companies will be constantly growing so i thought about maybe a function that uses the value of the first column to search for the tab and then get the value of G2.

I am really new to google sheet and i would appreciate any help on how to solve this problem

ANDREW EBARE
  • 85
  • 1
  • 9

1 Answers1

1

SUGGESTION

You can try this sample script below with custom function named getTotalWorkers & then add it as a bound script to your Spreadsheet file:

UPDATED Script:

function getTotalWorkers(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var names = ss.getRange("Company!A2:A").getValues().filter(String); //get names of the sheets on column A
  var res = [];
  for(x=0; x<names.length; x++){
    var data = ss.getRange(names[x]+"!G2").getValue(); //get the current cell G2 values on every sheet tabs
    res.push([data]); //place all values to a tem[orayr array variable
  }
  ss.getSheetByName("Company").getRange(2,6,res.length,1).setValues(res); //add the values under the "Workers" column on Company sheet tab
}

Sample Demonstration

After saving the script from the Apps Script editor, place the updated getTotalWorkers function to a time-driven trigger:

enter image description here

The time driven trigger will auto populate the "Workers" F column cells every minute (based on my sample time-driven trigger configuration):

enter image description here

SputnikDrunk2
  • 3,398
  • 1
  • 5
  • 17
  • It works perfectly, thanks a lot for the help. but i still have one question, How can i make it update automatically when there is any change on G2?. because now when a new companie is added it crashes and tells me nothing found in the "G2" range of the new created companie. even tho there is – ANDREW EBARE Nov 20 '21 at 07:04
  • The problem is that there's going to be a lot of tabs, and in G2 i receive the actual number that i have to put on the F2 of the companies tab. If i put a checkbox on each G2 i can't know the actual value that i have to parse to the companies tab under the giving company number of workers. Because every certain time the number in G2 is going to change . and i want to have the number changed aswell in the companies tab or i want to be notificated that a change has occur under a certain tab – ANDREW EBARE Nov 24 '21 at 19:18
  • **EDITED** Since this is a custom function, it won't update unless it's arguments changes. I have found that a [simple checkbox](https://stackoverflow.com/a/64711064) would be a better implementation. On the `Company` tab, just replace the current cell, where you have the custom formula, `F2` with `=IF(G2=FALSE,getTotalWorkers(A2:A),"")` and then add a single checkbox on cell G2 (**again, only on sheet tab called `Company`**), like this [sample image](https://imgur.com/a/FOVL1B1). Just press the checkbox to update the custom formula from time to time – SputnikDrunk2 Nov 24 '21 at 20:14
  • Jay G thanks a lot for this brilliant solution, for now i can work with the fact of manually updating the values with the checkbox. but do you think is possible for the values to be updated automatically when the value of any G2 tab is updated? because there're going to be a long list of companies adding or deleting new workers, and i would love to have that informetion on sync. is there any formula i can add to the G2 tabs that would update the function you wrote automatically? – ANDREW EBARE Nov 25 '21 at 17:05
  • I see. I have updated my answer's script code & implemented a time-driven trigger instead (as **set-&-forget** setup with no usage of custom function anymore). Just update the new code for the `getTotalWorkers` function & set it to a time-driver trigger from the Apps Script editor, either every minute or hour of your choice, [steps here](https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually). – SputnikDrunk2 Nov 25 '21 at 20:06
  • YEEEEEES. You are a Genius @Irvin Jay G. thanks a lot for taking your time to bring the best solution to this problem. – ANDREW EBARE Nov 27 '21 at 17:04