Може ли да се създава връзка чрез формула към друг Google Sheets файл?

Question

Аз много използвам Google Sheets - в работата ми само това е достъпно. Досега не съм намерила възможност за правене на препратки към друг файл (както е възможно в Ексел). Google Sheets позволява ли го като опция изобщо?

Благодаря!

Краси Кръстев · Accepted Answer

Здравей, Юлия.

Може, да се направи референция.

Когато става дума за референция в един файл, но различни листова (sheet) нещата стоят както в Excel.

Например:

Ако в sheet_2 (снимка 1 позиция 1) искам да прехвърля стойността от клетка A1 (снимка 1 позиция 2), в sheet_1

то синтаксиса би изглеждал така:

=sheet_2!A1 (снимка 2 позиция 1).

Ако обаче става дума за референция между два отделни файла, то тогава е малко по-интересно.

Използва се формулата  *=importrange("URL;"sheet_xxxxxxx!Диапазон") *

Как работи тя?

Отивам на изцяло нов файл в google docs (Документ_2) (снимка 3 позиция 1), в този файл искам да вмъкна част от информацията от другия документ (Doc) (снимка 4 позиция 1), която се намира в sheet_2 на документа Doc (снимка 4 позиция 2).

Започваме да изписваме формулата в клетка A1 на документа "Документ_2"

=importrange(

Първия параметър който трябва да въведа е URL адреса на документа от който искам да взема информацията - "Doc". На снимка 5 съм показал коя част от интернет адреса трябва да се копира.

Така стигнахме до:

=importrange("110vdImURRsDKOaTcuCpfbRJRQ8wZEN04DXW3uz8euzw";

следващия последен параметър, който трябва да въведем е диапзона от този файл който искаме да вземем информацията.

т.к. искам да вкараме числата въведени в sheet_2 на документа "Doc", то тогава записваме -  "sheet_2!A1:A3"

формулата стана:

=importrange("110vdImURRsDKOaTcuCpfbRJRQ8wZEN04DXW3uz8euzw";"sheet_2!A1:A3")

На снимка 6 е показан резултата в Документ_2

Това е начина за рефериране при google таблици.

Юлия Иванова · Answer

Уаааау, Краси, страхотен си! Сууупер много благодаря!!! Това ще изстреля работата ми на следващо ниво! :)

Имам тогава друг въпрос. Има ли автоматизиран начин в Ексел или Шийтс да се вкарват URL/референции към файлове?

Казусът е малко частен. Имам файлове, които представляват сертификати на хора, наименовани със съответното име на човека. Искам да ги изпратя по имейл, но за всеки човек да изпратя индивидуален имейл със съответния прикачен файл с неговото име. Обаче имам 200 човека (а в бъдеще ще имам още повече) и не искам да прекарам няколко часа в прикачване на файлове 1 по 1 в 200 отделни имейла.

Видях, че в Gmail това е възможно, НО трябва да имам таблица в Google Sheets, в която пак ръчно да въведа в колона срещу имената на хората съответстващия URL от качените в Гугъл Диск файлове... което не ми намалява обема от работа.

При положение, че съм използвала Mail merge в Word, за да създам самите сертификати и имам таблица с имената на хората и техните имейли, а заглавията на файловете ще ги направя да съответстват на имената на хората... ми се струва толкова логично да има вариант да направя автоматизирано пращането на такива персонализирани имейли... но не мога да намеря начин.

Опитах през Outlook, но понеже съм използвала опцията Letter, а не e-mail в Mail merge (за да създам .PDF) и после ми дава само възможност за принтиране, а не за изпращане по имейл.

Четох дали това може да стане чрез MailChimp, но и там обвързването на имената с файловете става ръчно...

Или аз не чета където трябва или пропускам някаква опция, функция, програма, мейл агент, макрос или нещо, което може да ми свърши работа.

Не знам дали въпросът ми е точно за тази тема, но може и да е. За варианта през Gmail, в който вкарвам URL на файловете (през add-on Mail merge) се опитах да избера в 1 клетка няколко URL и след това да ги split text to columns и после transpose, но ми се счупиха URL-ите по някаква причина и ми излиза грешка. Отделен е въпросът, че URL-ите не включват имената на файловете и не съм сигурна дали са правилно съотнесени след transpose и отново трябва да ги проверявам ръчно...

Много ви моля ако има вариант да се справя с тази автоматизация, ще ми бъде едно от най-полезните неща в бъдеще, защото ми се случва периодично да разпращам голям брой персонализирани имейли с различни прикачени файлове (грамоти и сертификати). Много благодаря за помощта! :)

Краси Кръстев · Answer

Здравей,

Не съм пробвал, т.к. наистина случая е малко частен.

Горното съм го прилагал в практиката  и затова го и споделих с пример.

Може да пробваш този скрипт тук: Script_URL чрез който поне това което видях е, че в един google sheet се праwqt две колони в една е името на файла (ако името на файла съвпадата с името на човека ще ти свърши работа) а в другата колона е URL -то.

Прегедай го и тествай, ако някой друг има опит и практика да споделя :) :) :) определено е интересен казус.

St. A. · Answer

Здравей, Юлия.

Мисля, че ето това -> https://developers.google.com/apps-script/articles/sending_emails е решение на твоята задача.

Поздрави!

St. A. · Answer

Или пък не :). Извинявам се.

St. A. · Answer

Юлия, ще помоля при положение че не е проблем за теб, да ми изпратиш примерна таблица с имена и е-мейли и да маркираш къде трябва да се появят линковете (може и да е екселски файл). Искам това, защото задачата е индивидуална и не мога да намеря готово решение, но пък ми е интересно и може да помогна.

St. A. · Answer

Папката с файловете е именувана "Test" (foldername = 'Test');
За да се вземе range информацията която ще се обработва започваме от втория ред (row = 2), първата колона (column = 1) и борй на редовете които ще се обработват (numRows = 5);
За да може да се опстнат имената на файловете и линковете към тях (за да се състави адреса на клетките в които ще се слагат) (columnName = 'B', columnLink = 'C');
В While има един if, който изключва името на GoogleSheet файлът, за да не се появява и той (за всеки случай);
След това проверяваме дали името от колоната Имена го има в списъкът от файлове IfNameExistInList(checkName, listWithNames);
Ако съществува име на файл като това от колоната с имена взимаме индекса на този файл от списъкът с файлове GetMatchingIndex(checkName, listWithNames);
Съставяме адреса на клетката за името на файлът и за линкът към файлът;
След това постваме информацията;

Това е линк към папката в моят драйв с GoogleSheet файлът и тестови текстови файлове.
Нужно е да се отвори Скрип Едитора и да се пусне скрипта, след което би трябвало келтки за име файл и линк да се попълнят.
Тай като за първи път работя с това чудо, не съм убеден че ще може да бъде пуснато през шерващият линк, но не пречи да се пробва.
Друг вариант е да си подготвите във Вашия дравй подходящ файл с подходящи файлове за обработка да копирате скрипта в скрипт едитор, като заместите необходимите променливи с тези във Вашия случай.
Според мен по добрия вариант е да се линкнат променливите към определени клетки в Sheet-а, за да може промяната им да е динамична;

Ето и link -> https://drive.google.com/drive/folders/1E6H0T1xvWZA024himeEKNw8Ip9kUvNO9?usp=sharing

За въпроси и допълнения съм насреща;

Забележка: Тай като ще работите с много имена има голяма вероятност някое име да съвпадне от тази гледна точка е добре да се правят проверки и по още поне един критерий.
ЕГН също не е удаче вариант, може някакво ваше служебно поле, гарантиращо неповтаряемост;

function listFolderContents() {
  var foldername = 'Test';

var folders = DriveApp.getFoldersByName(foldername)
  var folder = folders.next();
  var contents = folder.getFiles();

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var row = 2;
  var column = 1;
  var numRows = 5;
  var dataRange = sheet.getRange(row, column, numRows);
  var listOfNames = dataRange.getValues();

var file;
  var name;
  var link;
  var row = 2;
  var columnName = 'B';
  var columnLink = 'C';

while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    if (name != 'TestLinks')
    {
      if (IfNameExistInList(name.slice(0, -4), listOfNames))
      {
        var numberOfRowWhereNamesMatch = GetMatchingIndex(name.slice(0, -4), listOfNames);
        var cellNameAddress = columnName + (row + numberOfRowWhereNamesMatch);
        var cellLinkAddress = columnLink + (row + numberOfRowWhereNamesMatch);
        sheet.getRange(cellNameAddress).setValue(name);
        sheet.getRange(cellLinkAddress).setValue(link);
      }
    }
  }
}

function IfNameExistInList(checkName, listWithNames)
{
  var lengthOfList = listWithNames.length;

for (i = 0; i < lengthOfList; i++) { 
    if (checkName == listWithNames[i])
    {
      return true;
    }
  }
  return false;
}

St. A. · Answer

Видно съм нов и в Форума и не съм си прочел и правилата, за което поднасям извиненията си.
В шернатата папка има документ съдържащ скриптЪт.

Може ли да се създава връзка чрез формула към друг Google Sheets файл?

Вашият отговор