Jak sprawić, by Arkusze Google automatycznie aktualizowały odniesienie do innego arkusza?

Korzystam z zewnętrznej aplikacji do wprowadzania danych do arkusza kalkulacyjnego Google. Do tego skoroszytu odwołuje się następnie oddzielny skoroszyt (z IMPORTRANGE(spreadsheet_key, range_string)funkcją), który manipuluje i interpretuje dane. Mój problem polega na tym, że za każdym razem, gdy osobiście modyfikuję skoroszyt, do którego się odwołuje, skoroszyt odwołujący się aktualizuje, ale za każdym razem, gdy modyfikuje go zewnętrzna aplikacja, nie jest aktualizowany.

Próbowałem edytować „Ustawienia arkusza kalkulacyjnego”, aby przeliczanie odbywało się co minutę i przy każdej zmianie. Ponadto zainstalowałem również rozszerzenie w Google Chrome, które co godzinę automatycznie odświeża stronę. Jednak dane nie zostaną ponownie zaimportowane z odwołania do skoroszytu. Nawet jeśli skopiuję formułę do nowej komórki, dane nadal nie zostaną ponownie zaimportowane.

Czy jest coś, co mogę zrobić ze skoroszytem odniesienia, aby zmusić Arkusze Google do ponownego zaimportowania danych?

Edycja: Dla jasności mam obecnie jeden skoroszyt z danymi wprowadzonymi przez zewnętrzną aplikację (nazwij go „arkuszem źródłowym”) i inny skoroszyt z IMPORTRANGEfunkcją (nazwij go „arkuszem referencyjnym”). Dane pokazane przez IMPORTRANGEfunkcję w „arkuszu odniesienia” nie obejmują żadnych danych wprowadzonych przez zewnętrzną aplikację, ponieważ ostatnio osobiście edytowałem „arkusz źródłowy”. Ponadto oba skoroszyty korzystają z nowych Arkuszy Google.

Edycja: To pytanie nie jest takie samo jak Jak połączyć komórkę w Arkuszach kalkulacyjnych Google z komórką w innym dokumencie? ponieważ używam funkcji podanej jako rozwiązanie tego pytania do importowania danych z arkusza kalkulacyjnego. Problemem nie jest sposób importowania danych, ale sposób aktualizacji źródła danych. Zakładam, że Google zająłby się tym za mnie, ale dane w „arkuszu odniesienia” nie są aktualizowane, a jedynym sposobem na znalezienie aktualizacji jest fizyczny dostęp do „arkusza źródłowego” ”i sam go edytuj.

Walczę z tym samym problemem. Zamiast pisać funkcję niestandardową, dodaję inny ciąg kwerendy spreadsheet_urlw IMPORTRANGEw nadziei, że za każdym razem, gdy strona jest odświeżana, Google uważa, że musi pobrać dane z nowego arkusza. Po prostu dołączam nowy znacznik czasu, aby URL był niepowtarzalny za każdym razem. To rażący hack, ale działał dla mnie na wielu arkuszach.

Moja formuła poprzednio wyglądała mniej więcej tak:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

A teraz wygląda to tak:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Aktualizacja:

Ta metoda już nie działa, ponieważ Google nie zezwala już na to w now()środku importrange(). Zobacz komentarz Hugh poniżej.

Poniższe informacje pochodzą od Jimmy'ego w tej odpowiedzi na aplikacje internetowe .

1. =now()Powiedzmy, że w obu arkuszach kalkulacyjnych wstaw równanie do przypadkowej komórkiZ1
2. W obu arkuszach kalkulacyjnych wstaw =importrange()funkcję, która odwołuje się do nowfunkcji drugiego arkusza kalkulacyjnego.
3. Przejdź do ustawień arkusza kalkulacyjnego i wybierz przeliczanie co minutę.

Wypróbowałem wiele innych sugestii, w tym użycie =now()funkcji, teraz trik URL teraz w tym wątku lub Skrypt aplikacji do wstawiania losowego tekstu w ustalonych odstępach czasu, ale nic nie zmusiłoby importrange do aktualizacji oprócz ręcznej edycji arkusza źródłowego.

Odkryłem, że najprostszym sposobem było umieszczenie prostego ifstwierdzenia wokół importrange.

B1 = Sheet ID
B2 = Sheet Name and Range
B3 = Now()
=if (B3> now()-1, IMPORTRANGE(B1,B2),)

Działa to za każdym razem.

Obejście oznaczenia dat nie wydaje mi się działać - istnieje wyraźna uwaga, że ​​używanie datestamps i funkcji rand jest niedozwolone.

Moim bardzo pospiesznym obejściem jest usunięcie komórki, a następnie naciśnięcie klawiszy Ctrl + Z. Wymusza odświeżenie za każdym razem. Skryptowanie tego pojedynczego pliku lub w określonych odstępach czasu jest trywialne, aby zachować świeżość danych.

Znalazłem prosty hack do symulacji ręcznej aktualizacji komórki i uruchamiania modyfikacji w zewnętrznym arkuszu kalkulacyjnym.

1. Utworzono niestandardową funkcję taką jak ta:

   // Hack function used just to simulate a manual update
   function hack(value) {
     var list = []
     list.push(value);
     return list;
   }
   

2. Następnie na moim arkuszu nazywam to w ten sposób:

   =arrayformula(hack(tab!B1))
   

   gdzie tab!B1jest jedna z komórek, które modyfikuję kodem.

LUB ... możesz zrobić prosty skrypt, najpierw podając wartość cero (0), a następnie włączając formułę z powrotem, a to załatwi sprawę:

  var cell = sheet.getRange("B1:B1"); //SHEET TO INSERT VALUE
  cell.setFormula("=0");
   Utilities.sleep(2000); //JUST TO GIVE TIME TO UPDATE (OPTIONAL)
  var cell = sheet.getRange("B1:B1");
  cell.setFormula("=ImportRange(\"YOUR_SHEET_ID\",\"RANGE_TO_INSERT\")");  

Udało mi się znaleźć sposób na rozwiązanie mojego problemu (szczegółowo tutaj ) za pomocą skryptu aplikacji z niestandardową funkcją.

Jeśli zastąpić =IMPORTRANGE(spreadsheet_url, range_string)w arkuszu kalkulacyjnym z =DynamicImportRange(spreadsheet_url, sheet_name, range)i wklej poniższy kod w Narzędzia -> Script Editor -> pusty projekt, to powinno działać (zobacz to , aby uzyskać więcej informacji na temat pisania skryptów aplikacje).

/**
Usage: =DynamicImportRange(spreadsheet_url, sheet_name, range)
Compare to: =IMPORTRANGE(spreadsheet_url, range_string)

Where the given arguments are placeholders that should be replaced
by arguments specific to your use-case.
 */
function DynamicImportRange(sheet_url, sheet_name, sheet_range) {
  var values = SpreadsheetApp.openByUrl(sheet_url).getSheetByName(sheet_name).getRange(sheet_range).getValues();
  return values
};

/**
*/
function RefreshSheet() {
  // Update the following two variables to suit your particular situation
  var sheet_name = "sample_sheet_sheet"
  var range = "A1"

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  var formula1 = "Loading...";
  var formula2 = sheet.getRange(range).getFormula().toString().replace('"', '\"');
  // The following assumes that the data is being inserted into the sheet in the same
  // location as it is in in the source sheet.
  var form_range = formula2.split(",")[2].split("\"")[1];
  sheet.getRange(form_range).clear();
  sheet.getRange(range).setValue(formula1);
  Utilities.sleep(245);
  sheet.getRange(range).setValue(formula2);
};

Jeśli chcesz regularnie aktualizować arkusz, możesz ustawić wyzwalacz, przechodząc do opcji Zasoby -> Wyzwalacze bieżącego projektu w oknie Skryptu aplikacji.

Aby obejść tę now()blokadę, możesz zmienić ustawienia arkusza kalkulacyjnego, aby aktualizować je co godzinę.

Plik -> Ustawienia arkusza kalkulacyjnego -> Obliczenia

Zaktualizuj arkusz, aby przeliczyć się ponownie w „ Po zmianie i co godzinę ” lub „Po zmianie i co minutę”. Pamiętaj jednak, że wybranie opcji przeliczania co minutę może powodować zawieszanie się arkusza kalkulacyjnego.

Możesz użyć dodatku Arkusze Google Sheetgo, aby automatycznie zaktualizować swoje odniesienie z innego arkusza. Możesz użyć 30 aktualizacji bezpłatnie miesięcznie lub uzyskać płatną subskrypcję, aby uzyskać więcej aktualizacji. Ten film powinien wyjaśnić podstawowe użycie.