英文:
Paste to next available cell in column only works intermittently
问题
希望有人能帮忙解决下面的代码问题。
第一部分每次都能正常运行,但第二部分(即使它是一个副本,只更新了源和目标单元格),只能间歇性地正常运行。
// 复制并粘贴所选标签到“Hashtag Records”选项卡
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Hashtag Records'), true);
spreadsheet.getRange('B1').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getCurrentCell().offset(1, 0).activate();
spreadsheet.getRange('‘Hashtag Selector’!K5:K50').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
// 复制并粘贴日期作为“Hashtag Records”选项卡中的值
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Hashtag Records'), true);
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getCurrentCell().offset(1, 0).activate();
spreadsheet.getRange('‘Hashtag Selector’!O5:O50').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
预期结果:
- 这些部分(作为更大序列的一部分)应该将来自源“Hashtag Tracker”的2组数据复制到同一Google电子表格中的目标“Hashtag Records”
- 数据包括
- 要粘贴到B列中下一个可用单元格的标签列表 这段代码每次都能完美运行
- 要粘贴到A列中下一个可用单元格的日期 这段代码是从上面复制的(只更新了源和目标单元格)... 但它只能间歇性地正常运行 - 有时它会将日期正确粘贴到下一个可用单元格,其他时候它会将日期粘贴到A列中的较远位置(无法确定不一致的模式)
我尝试过不同的代码,来自这里和各种在线论坛(我很确定上面的代码是从这里复制的),但我花了太多时间尝试使其能够一直正常运行,真的会非常感激您的帮助。
我对脚本不是特别熟悉,所以如果有更好的选择,我愿意尝试其他代码...但剩下的列中有一些公式需要考虑(即需要粘贴数据的整行不是空的 - 只有A列和B列是完全空白的)。
非常感谢您提前的任何见解!
英文:
Hoping someone can help with the below coding please?
The first section works perfectly every time, but the 2nd section (even though it's a replica, with only source and destination cells updated), only works intermittently.
// Copy & Paste Selected Hashtags to "Hashtag Records" Tab
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Hashtag Records'), true);
spreadsheet.getRange('B1').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getCurrentCell().offset(1, 0).activate();
spreadsheet.getRange('\'Hashtag Selector\'!K5:K50').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
// Copy & Paste Dates as Values within "Hashtag Records" Tab
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Hashtag Records'), true);
spreadsheet.getRange('A1').activate();
spreadsheet.getCurrentCell().getNextDataCell(SpreadsheetApp.Direction.DOWN).activate();
spreadsheet.getCurrentCell().offset(1, 0).activate();
spreadsheet.getRange('\'Hashtag Selector\'!O5:O50').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);`
What's intended:
- The sections (part of a greater sequence) should copy 2 sets of data from source "Hashtag Tracker" to destination "Hashtag Records" within the same Google spreadsheet
- The data includes
- A list of hashtags that are to be pasted to the next available cell in column B This coding works perfectly every time
- Dates that are to be pasted to the next available cell in column A This coding was replicated from the above (with source and destination cells updated)... but it only works intermittently - sometimes it correctly pastes the dates to the next available cell, other times it pastes way down column A instead (unable to identify a pattern for the inconsistency)
I've tried different coding from both here and various online forums (I'm pretty sure the above was copied from here), but I've spent too much time trying to get it to work consistently, and would really appreciate some help please?
I'm not overly familiar with scripts, so I'm open to alternative coding if there's a better option... but there are formulas in the remaining columns that need to be taken into account (ie the entire row is not empty where the data needs to be pasted - only columns A and B are totally blank).
Thanks in advance for any insights!
答案1
得分: 1
I find all of the "Activate" stuff a result of using a recorded macro and is not required. In my opinion, good Google Spreadsheet App Script program is done using the Spreadsheet Service. Spreadsheet Service uses an instance of a Spreadsheet object and its methods to manipulate data. I use let
instead of var
.
Here is how I would do it.
// Copy & Paste Selected Hashtags to "Hashtag Records" Tab
let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let dest = spreadsheet.getSheetByName("Hashtag Records"); // destination sheet
let source = spreadsheet.getSheetByName("Hashtag Selector"); // source sheet
let range = dest.getRange("B1").getNextDataCell(SpreadsheetApp.Direction.DOWN); // last filled row of column B
range = range.offset(1,0); // next empty cell
source.getRange("K5:K50").copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
// Copy & Paste Dates as Values within "Hashtag Records" Tab
range = dest.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN); // last filled row of column A
range = range.offset(1,0); // next empty cell
source.getRange("O5:O50").copyTo(range, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
英文:
I find all of the "Activate" stuff a result of using a recorded macro and is not required. In my opinion good Google Spreadsheet App Script program is done using the Spreadsheet Service. Spreadsheet Service uses an instance of a Spreadsheet object and it methods to manipulate data. I use let
instead of var
.
Here is how I would do it.
// Copy & Paste Selected Hashtags to "Hashtag Records" Tab
let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
let dest = spreadsheet.getSheetByName("Hashtag Records"); // destination sheet
let source = spreadsheet.getSheetByName("Hashtag Selector"); // source sheet
let range = dest.getRange("B1").getNextDataCell(SpreadsheetApp.Direction.DOWN); // last filled row of column B
range = range.offset(1,0); // next empty cell
source.getRange("K5:K50").copyTo(range,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
// Copy & Paste Dates as Values within "Hashtag Records" Tab
range = dest.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN); // last filled row of column A
range = range.offset(1,0); // next empty cell
source.getRange("O5:O50").copyTo(range,SpreadsheetApp.CopyPasteType.PASTE_VALUES,false);
答案2
得分: 0
使用 Array.map() 来获取包含日期和标签的数组,并使用 Array.forEach() 或 appendRows_() 实用函数将该数组附加到目标表格,如下所示:
const ss = SpreadsheetApp.getActive();
const targetSheet = ss.getSheetByName('Hashtag Records');
const sourceData = ss.getRange('Hashtag Selector!K5:O50')
.getValues()
.map(row => [row[4], row[0],]);
appendRows_(targetSheet, sourceData, 1);
英文:
Use Array.map() to get an array with dates and tags, and Array.forEach() or the appendRows_() utility function to append that array to the target sheet, like this:
const ss = SpreadsheetApp.getActive();
const targetSheet = ss.getSheetByName('Hashtag Records');
const sourceData = ss.getRange('Hashtag Selector!K5:O50')
.getValues()
.map(row => [row[4], row[0],]);
appendRows_(targetSheet, sourceData, 1);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论